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.
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.Let's look at some examples of the MySQL CHAR()
string function:
USE mydatabase;
This query sets the context to the database named mydatabase
.
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
.
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.
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.
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.
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').
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.