Replace a newline in TSQL

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), '')

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

The Newline in T-SQL is represented by CHAR(13) & CHAR(10) (Carriage return + Line Feed). Accordingly, you can create a REPLACE statement with the text you want to replace the newline with.

REPLACE(MyField, CHAR(13) + CHAR(10), 'something else')

In SQL Server 2017 & later, use Trim

Select Trim(char(10) + char(13) from @str)

1. it trims on starting and ending, not in the middle
2. the order of \r and \n does not matter

I use it to trim special characters for a file name

Select Trim(char(10) + char(13) + ' *<>' from @fileName)

Sometimes

REPLACE(myString, CHAR(13) + CHAR(10), ' ')

won't work. In that case use the following snippet code:

REPLACE(REPLACE(myString, CHAR(13),''), CHAR(10), ' ')

To @Cerebrus solution: for H2 for strings "+" is not supported. So:

REPLACE(string, CHAR(13) || CHAR(10), 'replacementString')

I was wanting to sanitize the contents of a column to generate a csv file, so want to get rid of the comma (,) within the varchar as well as newline and carrage-return.

I also wanted to eventually use the generated csv to create another script (to insert rows into another db) so also needed to change ' within the varchar to '' so ended up with this...

REPLACE(REPLACE(REPLACE(REPLACE(ErrorMessage, CHAR(13), ''), CHAR(10), ''),',',''),'''','''''')

There may be other nicer ways but it got the job done.

The answer posted above/earlier that was reported to replace CHAR(13)CHAR(10) carriage return:

REPLACE(REPLACE(REPLACE(MyField, CHAR(13) + CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(10), 'something else')

Will never get to the REPLACE(MyField, CHAR(13) + CHAR(10), 'something else') portion of the code and will return the unwanted result of:

'something else''something else'

And NOT the desired result of a single:

'something else'

That would require the REPLACE script to be rewritten as such:

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(13) + CHAR(10), 'something else')

As the flow first tests the 1st/Furthest Left REPLACE statement, then upon failure will continue to test the next REPLACE statement.