CHAR Function in SQL Server

In this SQL Server tutorial, I will show you how to use the CHAR function in SQL Server to get the character based on the provided integer value.

You will learn about the syntax of the CHAR() function, and then find the character based on the provided code or integer value.

Finally, you will understand the control character and use the CHAR() function on the table column to insert the control characters.

Table of Contents

The CHAR function in SQL Server can help in various situations, such as when dealing with string concatenation, character manipulation, or generating sequences of characters based on the ASCII codes.

The CHAR() function takes the integer value, and based on the integer value it returns characters such as A, B, etc. The integer value is called ASCII codes that represent the specific characters.

The ASCII codes (integer value) range is from 0 to 255. In the ASCII set, each character is assigned a unique numeric code.

If you specify the integer value greater than 255, the CHAR() function returns the NULL value.

The syntax is given below.

CHAR(integer_exp)

Where,

CHAR(): The function name converts the given integer code to a character.
integer_exp: It is the integer expression or ASCII code of the character that you want to get back. The ASCII code ranges from 0 to 255.

For example, execute the below query to know the character of code 67.

SELECT CHAR(67);

Char Function in SQL Server ASCII Code 67

The above query returns the single-column result set with the value ‘C’ when you run it. The ASCII code 67 represents the uppercase letter ‘C’. You can get the character based on the code ranges from 0 to 255.

Now provide the code beyond 255 and see what the CHAR() function returns. For that use the below query.

SELECT CHAR(257);

Char Function in SQL Server ASCII Code Out of Range

As you can see in the above output, the CHAR() function returns NULL for the code 257 because the 257 is beyond the range of the ASCII code.

This is how to use the CHAR function in SQL Server to get the character based on the provided ASCII code.

Inserting Control Characters using CHAR Function in SQL Server

The control characters are not printable and don’t have a visible representation like other characters. But it decides how the text should be displayed. Many ASCII codes represent the control characters.

Some control characters are Backspace, Null, Line Feed, Vertical Tab, Form Feed, Carriage Return, Escape, etc.

Let’s take an example, suppose you have the Employees table which is shown below.

Inserting Control Characters using Char Function in SQL Server Table Employees

Now run the below query to combine the first and last names of the employees.

SELECT FirstName + LastName AS FullName FROM Employees;

Inserting Control Characters using Char Function in SQL Server Employees FullName

When you execute the above query, it returns a result set containing the first and last name of the employee such as RonanDoe, but the name is not very clear because there is no space between the first and last names of the employees.

So here you can use the CHAR() function to insert a control character which is space in this case. For that use the below query.

SELECT FirstName + CHAR(32) + LastName AS FullName FROM Employees;

Inserting Control Characters using Char Function in SQL Server

As you can see there is space between the first and last name of the employee such as Ronan Doe which is more readable now.

Here the query part (FirstName + CHAR(32) + LastName), this query part concatenates the FirstName, a space character (which is represented by CHAR(32)), and the LastName columns. The ‘+’ is the operator in SQL Server for string concatenation.

The query returns a new column FullName by concatenating the FirstName, a space, and the LastName for each row in the Employees table. The space character (CHAR(32)) is used to separate the first and last names with a space in the result set.

This is how to insert the control character using the CHAR() function in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how to retrieve the character based on the provided ASCII code to the CHAR() function in SQL Server. Also learned about the control character and inserted the control character with the table column value.