Replacing special characters by null

Question

Is there any function to replace the special characters by null in informatica

if we used replacestr function, i think we should specify all special characters

as follows replacestr(input,'!','~','@','#','$','%','^','&','*',null)

But we dont know what are teh special characters will be coming as input.

can u please let me know that which function will be suitable.

Answer 1

Option 1: Try this - REG_MATCH(input,'[^a-zA-Z0-9]')

Option 2: Try this - REG_REPLACE('[^[:print:]]', NULL)
This will find all non-print characters and replace them with null (I think it'd be better to replace with '')

Answer 2

Try this:

REG_REPLACE('[^[:print:]]',NULL)

This will find all non-print characters and replace them with null (I think it'd be better to replace with '', but thats just my opinion. Informatica uses POSIX-standard, so you can use others like alnum, punct, whatever you need.

http://en.wikipedia.org/wiki/Regular_expression#POSIX_Extended_Regular_Expressions