insert control characters in nvarchar or varchar from SQL script?

Question

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?

Answer 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.