Stripping special characters from a string

[system]

Hello All,

If I need to strip characters such as %, @ or # from a string would something like this work. For instance lets says I am reading a table and it has “Column1” with data in the format “xyz 123 IJK” or it can have data as “xyz $123 IJK”. if I wanted to remove the $ sign, do I have to first check the token to see if it has a $ sign and if it does the remove it, or can I just map “Column1” to replace_substr() function and that should take care of, i.e something like this.

replace_substr(‘Column1’, ‘$’, ‘’)

I read the documentation on this function and in the reference manual the first parameter is the actual string such as replace_substr(‘A penny saved is a penny earner’, ‘penny’, ‘million’). In my case I am not providing the actual or literal string but just the column name which contains the string and then I am providing the character i.e ‘$’ to search and then I dont provide anything for the replacement parameter, so it should just eliminate it.

Any thoughts, comments…Thanks.

---

brotherbob (BOB member since 2012-04-18)

[system]

Hi,

You can use SEARCH_REPLACE function for this case.

---

DbUoRdAsI (BOB member since 2012-05-08)

[system]

whataboutbobmaan, you are almost there! You can just map the function, however your syntax is slightly wrong:

@whataboutbobmaan

replace_substr(‘Column1’, ‘$’, ‘’)

Your script will try and replace any ‘$’ values in the string ‘Column1’ and will hence return ‘Column1’. To fix this do not encase the column name in quotes and for good practice include table name, e.g.:

replace_substr(TableName.Column1, '$', '')

There is also the replace_substr_ext function which you can look at, and also custom functions if you can utilise the function elsewhere.

For example, I lump together several varchar cleansing functions into a single custom function that I can then call from anywhere in the job with passing a variable or column to the varchar function parameter $P_TEXT (and then local $L_TEXT):

...
$L_TEXT=$P_TEXT;
...
# Remove special characters
$L_TEXT = replace_substr_ext($L_TEXT,'/n',' ',NULL,NULL); # Remove special characters - Newline
$L_TEXT = replace_substr_ext($L_TEXT,'/r',' ',NULL,NULL); # Remove special characters - Carriage Return
$L_TEXT = replace_substr_ext($L_TEXT,'/t',' ',NULL,NULL); # Remove special characters - Horizontal Tab
...
Return $L_TEXT;

---

Darth Services (BOB member since 2007-11-20)

[system]

Wrote a custom function for this a while back, like Darth uses – might find useful:

Replacing All Control Characters En Masse
http://www.etldoctor.com/2012/08/01/shady-characters/

---

JeffPrenevost (BOB member since 2010-10-09)

[system]

JeffPrenevost: Not able to open your custom function. Can you provide any other link. My requirement is I need to replace non -printable characters with Space mentioned in the below link

NON PRINTABLE CHARACTERS

Thanks
Santhosh

---

kolluri85 (BOB member since 2008-09-10)

[system]

@kolluri85

Jeff has already helpfully pasted all the code you need so you do not need to download the function really!

All you need to do is create your own function, add some variables (local and parameter), copy and paste the code and you are good to go.

---

Darth Services (BOB member since 2007-11-20)

[system]

@JeffPrenevost

Wrote a custom function for this a while back, like Darth uses – might find useful:

Replacing All Control Characters En Masse
http://www.etldoctor.com/2012/08/01/shady-characters/

Thanks for this! Works a treat!

---

LymedoBI (BOB member since 2013-08-08)

[system]

Hi,

I am stripping hex chars from an xml file.

The replace_substr_ext function works for most (search string set to find hex using ‘/x9999’ format) with the exception of NUL (’/x0000’).

Has anyone encountered this or am I missing something?

I’m suspecting that in my flat file format definition, under the ‘Default Format’ section, ‘NULL indicator’ is by default set to {none}. I’ve had no luck trying different values here either though.

Many thanks in advance for any help!

Darryl

---

dgoveas (BOB member since 2009-09-10)

[system]

Hi @JeffPrenevost, Please can you re-share the exact link to;

http://www.etldoctor.com/2012/08/01/shady-characters/

I am not able to open this link.
Thanks for your help in advance

---

BOBJFan (BOB member since 2011-09-24)

[system]

I used custom Function to strip any special character from a column values as below;

#$P_Input_Field parameter type is input (data type varchar(255) )

$return_str =$P_Input_Field;
$return_str = replace_substr($return_str,’',''); $return_str = replace_substr($return_str, ':', ''); $return_str = replace_substr($P_Input_Field,'’,’’);
$return_str = replace_substr($return_str, ‘~’, ‘’);
$return_str = replace_substr($return_str, ‘〜’,’’);
$return_str = replace_substr($return_str, ‘!’, ‘’);
$return_str = replace_substr($return_str, ‘！’,’’);
$return_str = replace_substr($return_str, ‘¡’, ‘’);
$return_str = replace_substr($return_str, ‘@’, ‘’);
$return_str = replace_substr($return_str, ‘#’,’’);
$return_str = replace_substr($return_str, ‘$’, ‘’);
$return_str = replace_substr($return_str, ‘¢’, ‘’);
$return_str = replace_substr($return_str, ‘£’, ‘’);
$return_str = replace_substr($return_str, ‘€’,’’);
$return_str = replace_substr($return_str, ‘Â¥’, ‘’);
$return_str = replace_substr($return_str, ‘%’, ‘’);
$return_str = replace_substr($return_str, ‘^’, ‘’);
$return_str = replace_substr($return_str, ‘&’, ‘’);
$return_str = replace_substr($return_str, ‘＆’,’’);
$return_str = replace_substr($return_str, ‘*’, ‘’);
$return_str = replace_substr($return_str, ‘＊’,’’);
$return_str = replace_substr($return_str, ‘(’, ‘’);
$return_str = replace_substr($return_str, ‘（’,’’);
$return_str = replace_substr($return_str, ‘)’, ‘’);
$return_str = replace_substr($return_str, ‘）’,’’);
$return_str = replace_substr($return_str, ‘-’, ‘’);
$return_str = replace_substr($return_str, ‘－’,’’);
$return_str = replace_substr($return_str, ‘_’, ‘’);
$return_str = replace_substr($return_str, ‘=’, ‘’);
$return_str = replace_substr($return_str, ‘+’, ‘’);
$return_str = replace_substr($return_str, ‘/’, ‘’);
$return_str = replace_substr($return_str, ‘／’,’’);
$return_str = replace_substr($return_str, ‘\’,’’);
$return_str = replace_substr($return_str, ‘|’, ‘’);
$return_str = replace_substr($return_str, ‘?’, ‘’);
$return_str = replace_substr($return_str, ‘？’,’’);
$return_str = replace_substr($return_str, ‘¿’, ‘’);
$return_str = replace_substr($return_str, ‘\[’,’’);
$return_str = replace_substr($return_str, ‘ï¼»’, ‘’);
$return_str = replace_substr($return_str, ‘]’, ‘’);
$return_str = replace_substr($return_str, ‘ï¼½’,’’);
$return_str = replace_substr($return_str, ‘\{’,’’);
$return_str = replace_substr($return_str, ‘｛’,’’);
$return_str = replace_substr($return_str, ‘}’, ‘’);
$return_str = replace_substr($return_str, ‘｝’,’’);
$return_str = replace_substr($return_str, ‘<’, ‘’);
$return_str = replace_substr($return_str, ‘〈’,’’);
$return_str = replace_substr($return_str, ‘>’, ‘’);
$return_str = replace_substr($return_str, ‘〉’,’’);
$return_str = replace_substr($return_str, ‘《’,’’);
$return_str = replace_substr($return_str, ‘》’,’’);
$return_str = replace_substr($return_str, ‘,’, ‘’);
$return_str = replace_substr($return_str, ‘，’,’’);
$return_str = replace_substr($return_str, ‘、’,’’);
$return_str = replace_substr($return_str, ‘.’, ‘’);
$return_str = replace_substr($return_str, ‘．’,’’);
$return_str = replace_substr($return_str, ‘;’, ‘’);
$return_str = replace_substr($return_str, ‘；’,’’);
$return_str = replace_substr($return_str, ‘:’, ‘’);
$return_str = replace_substr($return_str, ‘：’,’’);
$return_str = replace_substr($return_str, ‘\’,’’);
$return_str = replace_substr($return_str, ‘’’,’’);
$return_str = replace_substr($return_str, ‘"’,’’);
$return_str = replace_substr($return_str, ‘〃’,’’);
$return_str = replace_substr($return_str, ‘「’,’’);
$return_str = replace_substr($return_str, ‘」’,’’);
$return_str = replace_substr($return_str, ‘『’,’’);
$return_str = replace_substr($return_str, ‘』’,’’);
$return_str = replace_substr($return_str, ‘﹁’,’’);
$return_str = replace_substr($return_str, ‘﹂’,’’);
$return_str = replace_substr($return_str, ‘【’,’’);
$return_str = replace_substr($return_str, ‘】’,’’);
$return_str = replace_substr($return_str, ‘…’,’’);
$return_str = replace_substr($return_str, ‘ï½¥’, ‘’);
$return_str = replace_substr($return_str, ‘・’,’’);
$return_str = replace_substr($return_str, ‘〔’,’’);
$return_str = replace_substr($return_str, ‘〕’,’’);
$return_str = replace_substr($return_str, ‘〘’,’’);
$return_str = replace_substr($return_str, ‘〙’,’’);
$return_str = replace_substr($return_str, ‘〚’,’’);
$return_str = replace_substr($return_str, ‘〛’,’’);
$return_str = replace_substr($return_str, ‘。’,’’);

$return_str = replace_substr(replace_substr(rtrim_blanks(ltrim_blanks($return_str)),’ ', ’ '), ’ ', ’ ');
RETURN $return_str;

---

BOBJFan (BOB member since 2011-09-24)