Insert Control Characters In Nvarchar Or Varchar From Sql Script?

Given SQL Server 2012, how can I insert control characters (the ones coded under ASCII 32, like TAB, CR, LF) in a nvarchar or varchar column from a SQL script?

Solution 1:

Unless I miss something in the question you can do this using TSQL CHAR() function:

INSERT INTO MyTable(ColName) VALUES(CHAR(13) + CHAR(10))

Will insert CR/LF. Same for other codes.

Edit there is TSQL NCHAR() as well for Unicode characters.

Solution 2:

Please note that the function may vary depending on the type of your column, using the wrong function can result in wrong encoding.

nchar/nvarchar http://technet.microsoft.com/en-us/library/ms186939.aspx

char/varchar http://technet.microsoft.com/en-us/library/ms176089.aspx