Redshift: how to remove all newline characters in a field

Question

I am wondering how I can remove all newline characters in Redshift from a field. I tried something like this:

replace(replace(body, '\n', ' '), '\r', ' ')

and

regexp_replace(body, '[\n\r]+', ' ')

But it didn't work. Please share if you know how to do this.

Answer 1

Use chr(10) instead of \n

example:

select replace(CONCAT('Text 1' , chr(10), 'Text 2'), chr(10), '-') as txt

Answer 2

This should help

regexp_replace(column, '\r|\n', '')