Remove special characters from string using regexp_replace
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.