How to include or remove enter, line break, new line from a string - Postgresql

Question

How to remove or include "line break", "enter", "new line" from a string result in postgres.

The result is:

comentário
Texto do comentário
Mais texto

And I want to:

comentário
Texto do comentário - Mais texto

Or the other way around

Answer 1

To remove, we can use:

select regexp_replace(sua_coluna, E'[\\n\\r]+', ' - ', 'g' )

OR

select regexp_replace(sua_coluna, '[\n\r]+', ' - ', 'g' )

To includes a new use line:

select E'Primeira Linha\nSegunda linha.'

OR

select E'Primeira Linha'||chr(10)||'Segunda linha.'

Or as @David

select E'Primeira Linha'||chr(13)||'Segunda linha.'

Sources: https://stackoverflow.com/questions/7836906/how-to-remove-carriage-returns-and-new-lines-in-postgresql

https://stackoverflow.com/questions/26638615/insert-line-break-in-postgresql-when-updating-text-field

For more information, read the manual http://www.postgresql.org/docs/current/static/functions-matching.html