SQL CHAR Function

The SQL Server CHAR function converts the user-specified integer value (ASCII code) to a character. This CHAR function is exactly the opposite of the ASCII Function.

The syntax of the SQL CHAR Function is

SELECT CHAR (int_Expression)
FROM [Source]

int_Expression: Valid integer value or Expression to find the character. The specified integer value range should be between 0 and 255, and if you exceed the range, then it will return NULL as output.

The SQL Server CHAR function can be used to insert the control characters. This String function will return (1). The below table will show you some of the frequently used control characters.

Control CharacterValue
Line FeedCHAR(10)
Carriage returnCHAR(13)

SQL Server CHAR Function Example

It will convert the given integer value to the character. The following query may show you multiple ways to use this function. Please refer ASCII Table to check the SQL Server ASCII codes of each character.

In the first select statement, we are finding the characters at the integer values 75 and 105. Next, we used the integer value 204 directly inside it.

In the last line, we specified the string value 71, but the SQL CHAR Function is implicitly converting string 71 to integer 71 and returning the character at 71.


-- Initialize the variables.  
SET @x = 75
SET @y = 105
SET @z = 'T'

SELECT CHAR(@x) AS Result1,
       CHAR(@y) AS Result2,
       CHAR(ASCII(@Z)) AS Result3;

SELECT CHAR(204) AS Result4;  

SELECT CHAR('71') AS Result5;
CHAR Example 1

In this String Function example, We use the CHAR function inside the WHILE LOOP. Refer to the ASCII function, SUBSTRING, and WHILE LOOP articles.

--  Example
        @int_expression VARCHAR(50);  

-- Initialize the variables.  
SET @i = 1;  
SET @int_expression = '959871737882';
WHILE @i <= LEN(@int_expression)
		SELECT SUBSTRING(@int_expression, @i, 2) AS [ASCII_Value],
			CHAR(SUBSTRING(@int_expression, @i, 2)) AS [ASCII_Value]
		SET @i = @i + 2
SQL CHAR Function 2

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.