SQL Book

Learn what the SQL CHAR function does and see examples of how to use it

SQL Char function resultsets

In this article, we will explain the purpose of the SQL Char function and give you examples of how you may use it in your database projects.

What does the SQL Char function do?

The purpose of the SQL Char function is to receive a numeric ASCII code and return the equivalent character from the ASCII table that the ASCII code represents. Here we examine examples and functioning of the Char function in multiple databases.

Char function syntax

SQL Server


CHAR(ASCII_code)

The ASCII_code parameter that is passed to the function is an integer expression.

MySQL


CHAR(ASCII_code [, ...])

MySQL can accept more than one integer being passed to it and will return the character string equivalent for each integer passed to it. For example, if we pass in the 3 ASCII codes that represent the characters 'S', 'Q' and 'L' to a single call of the CHAR function then the function would return 'SQL'.


SELECT CHAR(83,81,76) As MyString

MyString
SQL

What is the valid range of integers that can be passed to the CHAR() function?

The ASCII table ranges from 0-128 with an extended table ranging from 129 - 255. Therefore it is valid to pass integer values from 0-255 to the ASCII function. If you pass in an integer outside this range then the function will return NULL.

What is SQL Char(13)?

ASCII_Code 13 is a carriage return and so passing 13 as the integer parameter to the Char function returns a new line when you append it to another string. 


SELECT 'First Line text' + CHAR(13) + 'Second line text' As MultilineString

This gives the following resultset (with the carriage return resulting in the concatenated string appearing over 2 lines.

MultilineString
First line text
Second line text

 

Codes 0 - 31 are control characters. Other common 'whitespace' codes are as follows:

 

ASCII Code Control Character
9 Tab
10 Line Feed
13 Carriage return

 

How to use the CHAR function to return a table that represents a list of ASCII codes and their associated characters.

We can use a loop to loop through all the numeric ASCII codes in the ASCII table to show their character values.


-- declare variable to use in the loop
DECLARE @i int = 33;
  
-- create a table variable to hold the list of codes
-- and their character equivalent
DECLARE @T TABLE (CharacterValue char(1), ASCIIValue int);
 
-- loop from 33 to 127
WHILE @i <= 127
BEGIN
	INSERT INTO @T (ASCIIValue,CharacterValue)
	VALUES (@i, CHAR(@i));

	SET @i = @i + 1;
END
-- select all rows from the table variable
SELECT * FROM @T;

This gives the following resultset. Note that we have omitted codes 0 - 31 in our loop because there is no entry in the CharacterValue column for these codes. This is because ASCII codes 0 - 31 represent commands (such as the carriage return example above for ASCII code 13) rather than characters.

 Common characters from the ASCII base table

ASCIIValue CharacterValue
33 !
34 "
35 #
36 $
37 %
38 &
39 '
40 (
41 )
42 *
43 +
44 ,
45 -
46 .
47 /
48 0
49 1
50 2
51 3
52 4
53 5
54 6
55 7
56 8
57 9
58 :
59 ;
60 <
61 =
62 >
63 ?
64 @
65 A
66 B
67 C
68 D
69 E
70 F
71 G
72 H
73 I
74 J
75 K
76 L
77 M
78 N
79 O
80 P
81 Q
82 R
83 S
84 T
85 U
86 V
87 W
88 X
89 Y
90 Z
91 [
92 \
93 ]
94 ^
95 _
96 `
97 a
98 b
99 c
100 d
101 e
102 f
103 g
104 h
105 i
106 j
107 k
108 l
109 m
110 n
111 o
112 p
113 q
114 r
115 s
116 t
117 u
118 v
119 w
120 x
121 y
122 z
123 {
124 |
125 }
126 ~
127  

Associated SQL String functions

It is useful to note that the function that performs the reverse functionality of the CHAR function is the ASCII function. The ASCII function takes a character expression (one or more characters) as its argument and returns the numeric ASCII code for that expression.

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.