SQL CHAR Function 1

SQL CHAR Function

by

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

SQL CHAR Function Syntax

The syntax of the SQL Server 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 the CHAR function will return NULL as output.

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

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

CHAR Function Example 1

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

DECLARE @x INT
DECLARE @y INT
DECLARE @z CHAR(2)

-- 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;
SQL CHAR Function 1

Here, we are finding the characters at the integer values 75 and 105.

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

Here, We used the integer value directly inside the Sql Server CHAR function

SELECT CHAR(204) AS Result4;

We specified the string value 71, but SQL is implicitly converting string 71 to integer 71 and returning the character at 71.

SELECT CHAR('71') AS Result5;

CHAR Function Example 2

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

DECLARE @i INT, 
        @int_expression VARCHAR(50);  

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