Sometime when working with strings in SQL Server, we need to do minor formatting like inserting line break or carriage return or tab to strings. These formatting are needed for several reasons like dynamically exporting data to a flat file, generating SQL scripts, etc… To represent special ASCII characters in SQL Server, we have to use the CHAR() function along with the ASCII number code. Here are some quick examples for using the special characters with strings.
These are the codes for line feed, carriage return and tab special characters.
- New Line / Line Break: Char(10)
- Carriage Return: Char(13)
- Tab: Char(9)
Inserting Line Break or New Line
In this example, I’ve generated a string with comma delimiters. Then the commas are replaced by the line break character CHAR(10). If you notice the result, it’s formatted with line break and finally it says 1 rows affected.
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100); SET @strInput = 'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday' /* Inserting Line Feed alone*/ SET @strResult = REPLACE(@strInput, ',', Char(10)) Select @strResult AS 'String with Line Feed' GO /* Result */ String with Line Feed ---------------------- Sunday Monday Tuesday Wednesday Thursday Friday Saturday (1 row affected)
Inserting Carriage Return
Inserting carriage return is similar to line break. You just need to replace CHAR(10) with CHAR(13). Here is the example.
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100); SET @strInput = 'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday' /* Inserting Carriage Return alone*/ SET @strResult = REPLACE(@strInput, ',', Char(13)) Select @strResult AS 'String with Carriage Return' GO /* Result */ String with Carriage Return ------------------------------- Sunday Monday Tuesday Wednesday Thursday Friday Saturday (1 row affected)
Inserting Tab
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100); SET @strInput = 'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday' /* Inserting Tab*/ SET @strResult = REPLACE(@strInput, ',', Char(9)) Select @strResult AS 'String with Tab' GO /* Result */ String with Tab -------------------------------------------------------------------------- Sunday Monday Tuesday Wednesday Thursday Friday Saturday (1 row affected)
Related Articles
Reference
- About CHAR() at Microsoft Docs.