plsql - Oracle Regexp to replace , and with space

Question

I am trying to select a column from a table that contains newline (NL) characters (and possibly others , , ). I would like to use the REGEXP to select the data and replace (only these three) characters with a space, " ".

Answer 1

No need for regex. This can be done easily with the ASCII codes and boring old TRANSLATE()

select translate(your_column, chr(10)||chr(11)||chr(13), '    ')
from your_table;

This replaces newline, tab and carriage return with space.

---

TRANSLATE() is much more efficient than its regex equivalent. However, if your heart is set on that approach, you should know that we can reference ASCII codes in regex. So this statement is the regex version of the above.

select regexp_replace(your_column,  '([x0A|x0B|`x0D])', ' ')
from your_table;

The tweak is to reference the ASCII code in hexadecimal rather than base 10.