Remove Tab, Newline Character From Data In SQL Server

In SQL Server 2005, 2008, 2012, If you are facing some inconsistency with data while selecting and other operations and find that this is due to tab or newline characters, then just replace them with blank.

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

CHAR(9) - Tab
CHAR(10) – LineFeed
Char(13) - CarriageReturn

CR ("carrige return") is ASCII code 13, and means "go back to the beginning of the line". It tells the Teletype machine to bring the print head to the left.

LF ("Line Feed") is ASCII code 10, and tells the printer to move the paper up 1 line.


Reference: Govind Badkur(http://sqlserver20.blogspot.com)