SQL Server String CHAR() Function

SQL Server CHAR() Function

The SQL Server CHAR() function is used to convert an ASCII code to its corresponding character. This function is useful for data processing and manipulation tasks where you need to work with ASCII values and their corresponding characters.

Syntax

SELECT CHAR(integer_expression);

The CHAR() function takes a single argument:

  • integer_expression: The ASCII code to be converted to a character.

Example SQL Server CHAR() Function Queries

Let's look at some examples of SQL Server CHAR() function queries:

1. Basic CHAR() Example

SELECT CHAR(65) AS character;

This query returns the character corresponding to the ASCII code 65. The result will be:

character
----------
A

2. CHAR() with a Column

SELECT employee_id, first_name, CHAR(SUBSTRING(ASCII(first_name), 1, 1)) AS first_char
FROM employees;

This query returns the first character of the first_name column for each employee. The result will show the employee_id, first_name, and the first character of first_name.

3. CHAR() with a Variable

DECLARE @asciiCode INT;
SET @asciiCode = 66;
SELECT CHAR(@asciiCode) AS character;

This query uses a variable to store an ASCII code and then converts it to its corresponding character. The result will be:

character
----------
B

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the CHAR() function.

Step 1: Creating a Table

This step involves creating a new table named ascii_table to store some sample data.

CREATE TABLE ascii_table (
    id INT PRIMARY KEY,
    ascii_code INT NOT NULL
);

In this example, we create a table named ascii_table with columns for id and ascii_code.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the ascii_table.

INSERT INTO ascii_table (id, ascii_code) VALUES (1, 65);
INSERT INTO ascii_table (id, ascii_code) VALUES (2, 66);
INSERT INTO ascii_table (id, ascii_code) VALUES (3, 67);

Here, we insert data into the ascii_table.

Step 3: Using the CHAR() Function

This step involves using the CHAR() function to convert the ASCII codes in the ascii_code column to their corresponding characters.

SELECT id, ascii_code, CHAR(ascii_code) AS character
FROM ascii_table;

This query retrieves the id, ascii_code, and the character corresponding to the ascii_code for each row in the ascii_table. The result will be:

id  ascii_code  character
--- ------------ ----------
1   65           A
2   66           B
3   67           C

Conclusion

The SQL Server CHAR() function is a powerful tool for converting ASCII codes to their corresponding characters. Understanding how to use the CHAR() function and its syntax is essential for effective data processing and manipulation in SQL Server.