Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Remove special characters from string using regexp_replace

plsql noviceDec 5 2007 — edited Apr 8 2013
Hi,
I'm writing a function to remove special characters and non-printable characters that users have accidentally entered into CSV files. I've looked at the ASCII character map, and basically, for every varchar2 field, I'd like to keep characters inside the range from chr(32) to chr(126), and convert every other character in the string to '', which is nothing.
Below is my draft PL/SQL statement:

.....(inside my function that takes an input string)
processed_string := regexp_replace(input_string, '( [^[:graph:] | ^[:blank:]] )', '');
......(rest of function)

I've tried this in SQLPlus (using select.... from dual) and using something like 'This is a test $%&^*&*' as input_string, and nothing is replaced. I also tried the following:

select regexp_replace('This is a test $%&^*&*', '( [[:graph:] | [:blank:]] )', '$') from dual;

And the whole string is turned into '$$$$$$$$...... So I'm guessing here (a BIG guess) the opposite MIGHT work too.

Do you think this statement will work? Since I don't know how to specify a range using chr() in regular expression, that's why I'm using posix. However, I'm new to this construct, and I'm having doubts especially with the '|' and '^' operators in the expression. Can you shed some light on this?

Many thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 6 2013
Added on Dec 5 2007
17 comments
286,936 views