TechNet
Export (0) Print
Expand All

CHAR (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Converts an int ASCII code to a character.

Topic link icon Transact-SQL Syntax Conventions

  
CHAR ( integer_expression )  

-- Azure SQL Data Warehouse and Parallel Data Warehouse  
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(1)

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

Control characterValue
Tabchar(9)
Line feedchar(10)
Carriage returnchar(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)

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

The following example assumes an ASCII character set and returns the character value for 6 ASCII character numbers.

SELECT CHAR(65) AS [65], CHAR(66) AS [66],   
CHAR(97) AS [97], CHAR(98) AS [98],   
CHAR(49) AS [49], CHAR(50) AS [50];  

Here is the result set.

65   66   97   98   49   50  
---- ---- ---- ---- ---- ----  
A    B    a    b    1    2  

D. Using CHAR to insert a control character

The following example uses CHAR(13) to return information about the databases on separate lines when the results are returned in text.

SELECT name, 'was created on ', create_date, CHAR(13), name, 'is currently ', state_desc   
FROM sys.databases;  
GO  

Here is the result set.

name     create_date    name    state_desc  
------------------------------------------------------------  
master                   was created on  2003-04-08 09:13:36.390   
master                   is currently  ONLINE  
tempdb                   was created on  2014-01-10 17:24:24.023   
tempdb                   is currently  ONLINE  
AdventureWorksPDW2012    was created on  2014-05-07 09:05:07.083 AdventureWorksPDW2012    is currently  ONLINE  

+ (String Concatenation) (Transact-SQL)
String Functions (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft