Oracle Regexp to replace \n,\r and \t with space

Question

I am trying to select a column from a table that contains newline (NL) characters (and possibly others \n, \r, \t). 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.

Answer 2

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

to clean it is essential to serve non-null value as params ... (oracle function basically will return null once 1 param is null, there are few excpetions like replace-functions)

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

this examples uses ' '->' ' translation as dummy-value to prevent Null-Value in parameter 3