CHAR (Transact-SQL)
SQL Server 2005
Converts an int ASCII code to a character.
Transact-SQL Syntax Conventions
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)
Reference
+ (String Concatenation) (Transact-SQL)String Functions (Transact-SQL)
