UPDATE: 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 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 .