Inserting Line Break or Carriage Return or Tab To String In SQL Server

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

• Segregating date part for datetime data type.

Reference

• About CHAR() at Microsoft Docs.