Export (0) Print
Expand All

CHAR (Transact-SQL)

Converts an int ASCII code to a character.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

CHAR ( integer_expression )

integer_expression

Is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.

CHAR can be used to insert control characters into character strings. The following table shows some frequently used control characters.

Control character

Value

Tab

char(9)

Line feed

char(10)

Carriage return

char(13)

A. Using ASCII and CHAR to print ASCII values from a string

The following example prints the ASCII value and character for each character in the string New Moon.

SET TEXTSIZE 0;
-- Create variables for the character string and for the current 
-- position in the string.
DECLARE @position int, @string char(8);
-- Initialize the current position and the string variables.
SET @position = 1;
SET @string = 'New Moon';
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)), 
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
   SET @position = @position + 1
   END;
GO

Here is the result set.

----------- -

78 N

----------- -

101 e

----------- -

119 w

----------- -

32

----------- -

77 M

----------- -

111 o

----------- -

111 o

----------- -

110 n

----------- -

B. Using CHAR to insert a control character

The following example uses CHAR(13) to print the name and e-mail address of an employee on separate lines when the results are returned in text. This example uses the AdventureWorks2012 database.

SELECT p.FirstName + ' ' + p.LastName, + CHAR(13)  + pe.EmailAddress 
FROM Person.Person p JOIN Person.EmailAddress pe
ON p.BusinessEntityID = pe.BusinessEntityID
AND p.BusinessEntityID = 1;
GO

Here is the result set.

Ken Sanchez

ken0@adventure-works.com

(1 row(s) affected)

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft