CHAR (Transact-SQL)
SQL Server 2008 R2
Converts an int ASCII code to a character.
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.
USE AdventureWorks2008R2; GO 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)
