MySQL CHAR() String Function

MySQL CHAR() String Function

The MySQL CHAR() string function returns the character for each integer passed to it. This function is essential for converting numeric ASCII codes to their corresponding characters in SQL queries.

Syntax

SELECT CHAR(number1, number2, ..., numberN) AS result
FROM table_name;

The CHAR() function has the following components:

  • number1, number2, ..., numberN: A list of integers to be converted to their corresponding characters.
  • result: An alias for the resulting characters.
  • table_name: The name of the table from which to retrieve the data.

Example MySQL CHAR() String Function

Let's look at some examples of the MySQL CHAR() string function:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE ascii_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code1 INT NOT NULL,
    code2 INT NOT NULL,
    code3 INT NOT NULL
);

This query creates a table named ascii_codes with columns for id, code1, code2, and code3.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO ascii_codes (code1, code2, code3)
VALUES (72, 101, 108),
       (77, 121, 83),
       (81, 76, 65),
       (66, 73, 84),
       (76, 69, 78);

This query inserts five rows into the ascii_codes table.

MySQL INSERT INTO TABLE

Step 4: Using CHAR() with WHERE Clause

Use the CHAR() function to retrieve characters for ASCII codes:

SELECT code1, code2, code3, CHAR(code1, code2, code3) AS characters
FROM ascii_codes;

This query retrieves the code1, code2, and code3 columns from the ascii_codes table and returns their corresponding characters.

MySQL CHAR() WITH WHERE CLAUSE

Step 5: Using CHAR() with Multiple Columns

Use the CHAR() function with multiple columns:

SELECT id, code1, code2, code3, CHAR(code1) AS char1, CHAR(code2) AS char2, CHAR(code3) AS char3
FROM ascii_codes;

This query retrieves the id, code1, code2, and code3 columns from the ascii_codes table and returns their corresponding characters individually.

MySQL CHAR() WITH MULTIPLE COLUMNS

Step 6: Using CHAR() with Constants

Use the CHAR() function with constants:

SELECT CHAR(72, 101, 108, 108, 111) AS hello, CHAR(87, 111, 114, 108, 100) AS world;

This query retrieves the characters for the ASCII codes 72, 101, 108, 108, 111 (which form 'Hello') and 87, 111, 114, 108, 100 (which form 'World').

MySQL CHAR() WITH CONSTANTS

Conclusion

The MySQL CHAR() function is a powerful tool for converting numeric ASCII codes to their corresponding characters in SQL queries. Understanding how to use the CHAR() function is essential for effective data querying and analysis in MySQL.