The SQL CHAR function is one of the SQL String Function, which is used to convert the user specified integer value (ASCII code) to a character. In this article we will show you, How to use or write SQL CHAR with example.
TIP: This function is exactly opposite to the SQL ASCII Function
SQL CHAR Function Syntax
The basic syntax of the SQL Server CHAR Function is as shown below:
|
1 2 |
SELECT CHAR (int_Expression) FROM [Source] |
int_Expression: Please specify the valid integer value or Expression for which you want to find the character. The specified integer value range should be between 0 and 255, and if you exceed the range then, CHAR function will return NULL as output.
TIP: Please refer ASCII Table to check the ASCII codes of each and every character.
The SQL Server CHAR function can be used to insert the control characters. Below table will show you some of the most frequently used control characters.
| Control Character | Value |
|---|---|
| Tab | CHAR(9) |
| Line Feed | CHAR(10) |
| Carriage return | CHAR(13) |
Return Type
This function will return CHAR(1)
SQL CHAR Function Example 1
The CHAR Function is used to convert the given integer value to character. The following query may show you the multiple ways to use this function.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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; |
OUTPUT
ANALYSIS
Below lines of code is used to declare three variables of type CHAR and Integer. Next, we assigning the values.
|
1 2 3 4 5 6 7 8 |
DECLARE @x INT DECLARE @y INT DECLARE @z CHAR(2) -- Initialize the variables. SET @x = 75 SET @y = 105 SET @z = 'T' |
From the below statement you can see, we are finding the characters at the integer values 75 and 105. We also assigned new names to the result as ‘Result1’, and ‘Result2’ using ALIAS Column.
|
1 2 3 |
SELECT CHAR(@x) AS Result1, CHAR(@y) AS Result2, CHAR(ASCII(@Z)) AS Result3; |
In the next line, We used the integer value directly inside the CHAR function
|
1 |
SELECT CHAR(204) AS Result4; |
You can see from the below statement, We specified the string value 71 but SQL is implicitly converting string 71 to integer 71, and returning the character at 71.
|
1 |
SELECT CHAR('71') AS Result5; |
SQL CHAR Function Example 2
In this example, We are going to use the CHAR function inside the WHILE LOOP. I suggest you to refer both the SUBSTRING and WHILE LOOP articles to understand the query execution.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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; |
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply