A Complete Guide to the MySQL CHAR() Function

A comprehensive guide to the MySQL CHAR() function, including syntax, examples, and use cases.

Posted on

The MySQL CHAR() function is used to return the character representation of a given integer or a series of integers. This function is particularly useful when you need to convert ASCII values into their corresponding characters, which can be helpful in data formatting or when working with character encodings.

Syntax

CHAR(N[, N2, ...])
  • N: An integer value representing the ASCII code of the character you want to return.
  • N2, ...: Additional integer values for which you want to return characters.

Examples

Basic Usage

SELECT CHAR(65) AS Character;  -- Returns 'A'
SELECT CHAR(66, 67) AS Characters; -- Returns 'BC'
SELECT CHAR(97, 98, 99) AS Characters; -- Returns 'abc'
SELECT CHAR(48, 49, 50) AS Characters; -- Returns '012'
SELECT CHAR(32, 33) AS Characters; -- Returns ' !'

Using CHAR with Multiple Values

SELECT CHAR(65, 66, 67) AS Characters; -- Returns 'ABC'
SELECT CHAR(97, 98, 99) AS Characters; -- Returns 'abc'
SELECT CHAR(48, 49, 50, 51) AS Characters; -- Returns '0123'
SELECT CHAR(32, 33, 34) AS Characters; -- Returns ' !"'

CHAR with Special Characters

SELECT CHAR(10) AS NewLine; -- Returns a newline character
SELECT CHAR(9) AS Tab; -- Returns a tab character
SELECT CHAR(13) AS CarriageReturn; -- Returns a carriage return character

Using CHAR in a Table

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(255)
);
INSERT INTO messages (content) VALUES ('Hello'), ('World'), ('MySQL CHAR Function');

SELECT id, content, CHAR(65) AS FirstChar FROM messages;

This query will return the id, original content, and the character ‘A’ for each row. The output will look like this:

+----+-------------------------+-----------+
| id | content                 | FirstChar |
+----+-------------------------+-----------+
|  1 | Hello                   | A         |
|  2 | World                   | A         |
|  3 | MySQL CHAR Function     | A         |
+----+-------------------------+-----------+

Practical Use Cases

The CHAR() function can be particularly useful in scenarios such as:

  • Data Formatting: Converting ASCII values to characters for display purposes.

    SELECT CHAR(72, 101, 108, 108, 111) AS Greeting; -- Returns 'Hello'

  • Character Encoding: Working with character encodings where you need to convert numeric values to their corresponding characters.

    SELECT CHAR(65 + 1) AS NextChar; -- Returns 'B' (ASCII 66)

  • Generating Dynamic Strings: Creating strings dynamically based on ASCII values, which can be useful in reports or data exports.

    SELECT CONCAT(CHAR(65), CHAR(66), CHAR(67)) AS Alphabet; -- Returns 'ABC'

CHAR with Unicode Characters

SELECT CHAR(128512) AS SmileyFace; -- Returns '😀'
SELECT CHAR(128513) AS GrinningFace; -- Returns '😁'
SELECT CHAR(128514) AS LaughingFace; -- Returns '😂'

CHAR in Calculations

You can also use the CHAR() function in calculations or concatenations. For example, if you want to create a string that includes both text and characters:

SELECT CONCAT('Character A is: ', CHAR(65)) AS Result; -- Returns 'Character A is: A'

CHAR with Hexadecimal Values

You can also use the CHAR() function with hexadecimal values by converting them to decimal. For example, if you want to convert a hexadecimal value to its character representation:

SELECT CHAR(CONV('41', 16, 10)) AS HexToChar; -- Returns 'A'
SELECT CHAR(CONV('42', 16, 10)) AS HexToChar; -- Returns 'B'
SELECT CHAR(CONV('43', 16, 10)) AS HexToChar; -- Returns 'C'

CHAR with Binary Values

You can also use the CHAR() function with binary values by converting them to decimal. For example, if you want to convert a binary value to its character representation:

SELECT CHAR(CONV('01000001', 2, 10)) AS BinaryToChar; -- Returns 'A'
SELECT CHAR(CONV('01000010', 2, 10)) AS BinaryToChar; -- Returns 'B'
SELECT CHAR(CONV('01000011', 2, 10)) AS BinaryToChar; -- Returns 'C'

CHAR with Escape Sequences

You can also use the CHAR() function with escape sequences to include special characters in your strings. For example, if you want to include a newline or tab character:

SELECT CHAR(10) AS NewLine; -- Returns a newline character
SELECT CHAR(9) AS Tab; -- Returns a tab character
SELECT CHAR(13) AS CarriageReturn; -- Returns a carriage return character

CHAR with Conditional Logic

You can also use the CHAR() function in conditional logic, such as in a CASE statement:

SELECT
    id,
    content,
    CASE
        WHEN CHAR_LENGTH(content) > 10 THEN CHAR(65) -- 'A' for long content
        ELSE CHAR(66) -- 'B' for short content
    END AS ContentType
FROM messages;

This query will return the id, original content, and a character based on the length of the content. The output will look like this:

+----+-------------------------+-------------+
| id | content                 | ContentType |
+----+-------------------------+-------------+
|  1 | Hello                   | B           |
|  2 | World                   | B           |
|  3 | MySQL CHAR Function     | A           |
+----+-------------------------+-------------+

CHAR with String Concatenation

You can also use the CHAR() function in string concatenation to create more complex strings:

SELECT CONCAT('Character A is: ', CHAR(65), ' and Character B is: ', CHAR(66)) AS Result; -- Returns 'Character A is: A and Character B is: B'

CHAR with String Formatting

You can use the CHAR() function to format strings in a specific way, such as adding padding or special characters:

SELECT LPAD(CHAR(65), 5, '0') AS PaddedChar; -- Returns '000A'
SELECT RPAD(CHAR(66), 5, ' ') AS PaddedChar; -- Returns 'B    '
SELECT CONCAT(CHAR(67), ' is the third letter') AS FormattedString; -- Returns 'C is the third letter'

CHAR with JSON Data

You can also use the CHAR() function with JSON data to extract or format characters:

SELECT JSON_OBJECT('charA', CHAR(65), 'charB', CHAR(66)) AS JsonData; -- Returns '{"charA": "A", "charB": "B"}
SELECT JSON_ARRAY(CHAR(67), CHAR(68), CHAR(69)) AS JsonArray; -- Returns '["C", "D", "E"]'

CHAR with Regular Expressions

You can also use the CHAR() function in conjunction with regular expressions to match specific patterns:

SELECT content
FROM messages
WHERE content REGEXP CONCAT('^', CHAR(65), '.*'); -- Matches content starting with 'A'

CHAR with User-Defined Functions

You can also create user-defined functions that utilize the CHAR() function for more complex operations:

CREATE FUNCTION GetCharFromAscii(ascii INT) RETURNS CHAR(1)
BEGIN
    RETURN CHAR(ascii);
END;

This function can be used to get the character representation of an ASCII value:

SELECT GetCharFromAscii(65) AS Character; -- Returns 'A'
SELECT GetCharFromAscii(66) AS Character; -- Returns 'B'
SELECT GetCharFromAscii(67) AS Character; -- Returns 'C'

CHAR with Stored Procedures

You can also use the CHAR() function within stored procedures to perform operations on character data:

DELIMITER //
CREATE PROCEDURE GetChar(IN ascii INT)
BEGIN
    SELECT CHAR(ascii) AS Character;
END //
DELIMITER ;

You can call this stored procedure to get the character representation of an ASCII value:

CALL GetChar(65); -- Returns 'A'
CALL GetChar(66); -- Returns 'B'
CALL GetChar(67); -- Returns 'C';

Conclusion

The MySQL CHAR() function is a versatile tool for converting ASCII values into their corresponding characters. It can be used in various scenarios, including data formatting, character encoding, and dynamic string generation. By understanding how to use the CHAR() function effectively, you can enhance your SQL queries and improve data manipulation in your MySQL databases. Whether you’re working with simple strings or complex data structures, the CHAR() function provides a powerful way to handle character data in MySQL.