Remove NewLine characters from the data in SQL Server

UPDATE: Xavid  at 8/2/2007  in a comment suggested a simpler code:

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ”), CHAR(13), ”), CHAR(9), ”)


I found that some string in the database have NewLine characters where they do not required.

To remove them in T-SQL I wrote the following SQL script (TODO: write  re-usable SP, also special option to remove NewLine characters from the end of the string)

declare @NewLine char(2)
set @NewLine=char(13)+char(10)
update TableName
     set ColumnName =Replace(ColumnName , @NewLine,”)
WHERE ColumnName like ‘%’ +@NewLine +’%’

Note that even if WHERE condition may  look redundant,  it is important for performance. Without the (ColumnName like ‘%’ +@NewLine +’%’)condition all records will be updated,even if actual column value would not be changed.

To identify rows with newLine at the end the following condition can be used.

where ( RIGHT(ColumnName ,2)=@NewLine

See ASCII Characters for values of different characters .