CHAR (T-SQL)

A string function that converts an int ASCII code to a character.

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 table shows some commonly used control characters.

Examples

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

This 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. Use CHAR to insert a control character

This example uses CHAR(13) to print name, address, and city information on separate lines.

USE Northwind

SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,
    + CHAR(13)+ City, Region

FROM Employees

WHERE EmployeeID = 1

Here is the result set:

Nancy Davolio

507 - 20th Ave. E.

Apt. 2A

Seattle            WA

Note In this record, the data in the Address column also contains a control character.

See Also