SQL CHAR Function

The SQL CHAR function is used to convert an ASCII code into its corresponding character.

This function is the opposite of the SQL ASCII function, which retrieves the ASCII code for a character.

The CHAR function is useful when you need to convert ASCII values to characters, such as when dynamically generating characters based on numerical codes.

In this tutorial, we will go through SQL CHAR String function, its syntax, and how to use this function in SQL statements, with the help of well detailed examples.

Syntax of SQL CHAR Function

The basic syntax of the SQL CHAR function is:

</>
Copy
SELECT CHAR(ascii_code) AS character_value;

Each part of this syntax has a specific purpose:

  • CHAR(ascii_code): Converts the ASCII code into its corresponding character.
  • AS character_value: Assigns an alias to the result for easier reference in output.

Examples for SQL CHAR with MySQL

We’ll go through various examples demonstrating the CHAR function in MySQL. Using MySQL 8.0 with MySQL Workbench, we will apply the CHAR function to different ASCII values and evaluate their corresponding characters.

1. Converting an ASCII Code to a Character

To get the character for ASCII code 65:

</>
Copy
SELECT CHAR(65) AS character_value;

This query returns 'A', which is the character for ASCII code 65.

SQL CHAR Function Example - Converting an ASCII Code to a Character

If you’re seeing a BLOB output, it could be due to the format or encoding settings in your database client. Try using CHAR(65) USING utf8 or explicitly converting the BLOB to a readable character.

</>
Copy
SELECT CAST(CHAR(65) AS CHAR) AS character_value;
SQL CHAR Function Example - Converting an ASCII Code to a Character - Fixing blob to display as char

2. Using CHAR for Multiple ASCII Codes

To retrieve the characters for ASCII codes 97 (lowercase “a”) and 33 (exclamation mark):

</>
Copy
SELECT CAST(CHAR(97) AS CHAR) AS character_a, CAST(CHAR(33) AS CHAR) AS character_exclamation;

This query returns 'a' for 97 and '!' for 33.

SQL CHAR Function Example -

3. Concatenating Characters from ASCII Codes

To form the string “HELLO” by converting ASCII codes for each letter:

</>
Copy
SELECT CAST(CONCAT(CHAR(72), CHAR(69), CHAR(76), CHAR(76), CHAR(79)) AS CHAR) AS greeting;

This query converts ASCII codes to form the word 'HELLO' by concatenating characters for each letter.

SQL CHAR Function Example - Concatenating Characters from ASCII Codes

4. Using CHAR in Conditional Statements

To use CHAR in a conditional statement, let’s retrieve rows from a table and check if the ASCII code is 65 (uppercase “A”):

</>
Copy
SELECT name,
CASE WHEN ASCII(SUBSTRING(name, 1, 1)) = 65 THEN CAST(CHAR(65) AS CHAR)
ELSE CAST('Not A' AS CHAR)
END AS starts_with_a
FROM students;

This query returns 'A' for names that start with an uppercase “A” and 'Not A' for others.

SQL - Using CHAR in Conditional Statements

FAQs for SQL CHAR Function

1. What does the SQL CHAR function do?

The CHAR function converts an ASCII code to its corresponding character, allowing numeric codes to be displayed as characters.

2. Can CHAR handle multiple ASCII codes?

Yes, you can use CONCAT to join multiple CHAR values from ASCII codes into a single string.

3. Does CHAR support non-printable ASCII codes?

Yes, CHAR supports non-printable ASCII codes, although some may not be visually displayed in output.

4. How does CHAR handle invalid ASCII codes?

If an invalid ASCII code is passed, CHAR may return NULL or an error, depending on the SQL database being used.

5. What is the ASCII code range that CHAR can handle?

The CHAR function typically handles ASCII codes from 0 to 127, though some databases may support extended ASCII codes up to 255.