REGEXP_REPLACE in Oracle replacing character 'n' and 'r' instead of replacing new line or space

Question

SELECT 

COALESCE(TRIM(REGEXP_REPLACE(Col_1, '(<.*?>)|(/(\r\n)+|\r+|\n+|Chr(9)+/i)|(/^\s+|\s+$|\s+(?=\s))', ' ',1,0, 'm')), TRIM(REGEXP_REPLACE(Col2, '(<.*?>)|(/(\r\n)+|\r+|\n+|Chr(9)+/i)|(/^\s+|\s+$|\s+(?=\s))', ' ', 1, 0, 'm'))) AS SNDR_TO_RCVR_INF1

FROM TABLE_NAME.

This replaces the characters 'r' or 'n' from Col_1 or Col 2 instead of new line.

I guess it is because I used \r and \n in the syntax.

But then what should I do so the syntax recognizes /n instead of n (newline) or /r instead of r?

Answer 1

You can concatenate in the character sequences for carriage return and new line (and tab) instead:

'(<.*?>)|(/(' ||chr(13)||chr(10)|| ')+|' ||chr(13)|| '+|' ||chr(10)|| '+|' ||chr(9)|| '+/i)|(/^\s+|\s+$|\s+(?=\s))'

db<>fiddle

You can probably simplify that further, but that's a separate issue...