Replace a newline in TSQL

Peter picture Peter · Jun 4, 2009 · Viewed 575.9k times · Source

I would like to replace (or remove) a newline character in a TSQL-string. Any Ideas?

The obvious

REPLACE(@string, CHAR(13), '')

just won't do it...

Answer

RBarryYoung picture RBarryYoung · Jun 4, 2009

Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this:

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')