CHAR (Transact-SQL)

Converts an int ASCII code to a character.

Transact-SQL Syntax Conventions

Syntax

CHAR ( integer_expression )

Arguments

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

Return Types

char(1)

Remarks

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)

Examples

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 name, e-mail address, and telephone number on separate lines when the results are returned in text.

USE AdventureWorks;
GO
SELECT FirstName + ' ' + LastName, + CHAR(13)  + EmailAddress + CHAR(13) 
+ Phone
FROM Person.Contact

WHERE ContactID = 1;
GO

Here is the result set.

Gustavo Achong
gustavo0@adventure-works.com
398-555-0132

(1 row(s) affected)

See Also

Reference

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

Help and Information

Getting SQL Server 2005 Assistance