DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to detect carriage return and line feed (CR/LF)?

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE