Execute the following SQL Server T-SQL script in SSMS Query Editor to detect special line ending characters; Carriage Return ( CR - CHAR(13) ) and Line Feed (LF - CHAR(10) ).

The terminology originates in the typewriter's operation. Carriage Return returns the carriage with the typing paper to the left margin. Line Feed rotates the carriage upward to advance the typing paper to the next line, the paper stays at the current position. The usual operation is Carriage Return - Line Feed (CR-LF) together, the typing paper positioned the left margin and next line.

In Computer Science CR/LF is used as a record (line) terminator .

-- Detect special non-printing characters (white space) the following way

DECLARE @WhiteSpace varchar(64) = 'New York'+' '+char(13)+char(10)+'City'

SELECT ExposeWP=CONVERT(varbinary(max), @WhiteSpace), NonPrint=@WhiteSpace

/* ExposeWP NonPrint

0x4E657720596F726B200D0A43697479    New York   City  */

-- Hex 20 is space, 0D and 0A are printing as space but they are CRLF

------------

USE tempdb;

SELECT * INTO Product FROM AdventureWorks.Production.Product

GO

UPDATE Product

SET Name = STUFF (Name, 3, 0, CHAR(13))

WHERE ProductID = 800

UPDATE Product

SET Name = STUFF (Name, 14, 0, CHAR(10))

WHERE ProductID = 888

GO

SELECT ProductID, ProductName = Name

FROM Product

WHERE Name LIKE '%' + CHAR(10) + '%'

OR Name LIKE '%' + CHAR(13) + '%'

GO

/* Results

ProductID   ProductName

800   Ro ad-550-W Yellow, 44

888   HL Touring Fr ame - Yellow, 50

*/

DROP TABLE Product

GO

------------

Related article:

How to insert a line break in a SQL Server VARCHAR/NVARCHAR string