BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: Upcoming Events: PGHBOUG: Nov 1.

Stripping special characters from a string


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
brotherbob
Senior Member
Senior Member



Joined: 17 Apr 2012

Posts: 90



PostPosted: Tue Aug 13, 2013 10:33 am 
Post subject: Stripping special characters from a string

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.
Back to top
DbUoRdAsI
Principal Member
Principal Member



Joined: 08 May 2012

Posts: 119


flag
PostPosted: Tue Aug 13, 2013 11:45 pm 
Post subject: Re: Stripping special characters from a string

Hi,

You can use SEARCH_REPLACE function for this case.
Back to top
Darth Services
Principal Member
Principal Member



Joined: 20 Nov 2007

Posts: 342
Location: Death Star Schema


flag
PostPosted: Wed Aug 14, 2013 3:30 am 
Post subject: Re: Stripping special characters from a string

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

whataboutbobmaan wrote:

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.:

Code:

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):

Code:

...
$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;

_________________
BODS / BOE / BOBIP

SQL Server 2005, 2008, 2012, 2014 / Oracle 11g, 12c / Windows / Solaris
Back to top
JeffPrenevost
Principal Member
Principal Member



Joined: 09 Oct 2010
ASUG Icon
Posts: 127
Location: SE Michigan, Ann Arbor area


flag
PostPosted: Wed Aug 14, 2013 4:15 am 
Post subject: Re: Stripping special characters from a string

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/

_________________
Best wishes,
Jeff Prenevost
Sr. Implementation Architect, Guidewire Software
Back to top
kolluri85
Forum Member
Forum Member



Joined: 10 Sep 2008

Posts: 6


flag
PostPosted: Thu Aug 15, 2013 10:12 am 
Post subject: Re: Stripping special characters from a string

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
http://web.itu.edu.tr/~sgunduz/courses/mikroisl/ascii.html

Thanks
Santhosh
Back to top
Darth Services
Principal Member
Principal Member



Joined: 20 Nov 2007

Posts: 342
Location: Death Star Schema


flag
PostPosted: Thu Aug 15, 2013 10:29 am 
Post subject: Re: Stripping special characters from a string

@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.

_________________
BODS / BOE / BOBIP

SQL Server 2005, 2008, 2012, 2014 / Oracle 11g, 12c / Windows / Solaris
Back to top
LymedoBI
Senior Member
Senior Member



Joined: 08 Aug 2013

Posts: 62
Location: Manchester UK



PostPosted: Thu Jan 23, 2014 11:03 am 
Post subject: Re: Stripping special characters from a string

JeffPrenevost wrote:
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! icon_biggrin.gif
Back to top
dgoveas
Senior Member
Senior Member



Joined: 10 Sep 2009
ASUG Icon
Posts: 46
Location: Canada


flag
PostPosted: Wed Aug 10, 2016 3:23 pm 
Post subject: Re: Stripping special characters from a string

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? banghead.gif

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
Back to top
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Thu Aug 15, 2019 9:23 am 
Post subject: Re: Stripping special characters from a string

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
Back to top
BOBJFan
Principal Member
Principal Member



Joined: 24 Sep 2011

Posts: 138



PostPosted: Wed Aug 28, 2019 4:37 am 
Post subject: Re: Stripping special characters from a string

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;
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0300 seconds using 17 queries. (SQL 0.0023 Parse 0.0010 Other 0.0267)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo