Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

CHAR

SQL Server 2000

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.

Control character Value
Tab CHAR(9)
Line feed CHAR(10)
Carriage return CHAR(13)

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, when the results are returned in text.

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

+ (String Concatenation)

String Functions

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft