How to use the CHR() function in SQL

Overview

The CHR() function returns a character corresponding to the ASCII number passed.

Syntax

CHR(value)

Parameter

value: This indicates the integer ASCII number that has to be converted to a character.

Example

Let’s make a query to find the writer’s names that starts with M and converts their id to a character.

The following code shows how we can solve this using the CHR() function in SQL.

CREATE TABLE Writer (
    id int,
    name varchar(50),
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Writer
VALUES (101,'Sharon Peller','Female','Kogi');
INSERT INTO Writer
VALUES (102,'Paul Dons','Male','Lagos');
INSERT INTO Writer
VALUES (103,'Ameera Abedayo','Female','Imo');
INSERT INTO Writer
VALUES (104,'Maria Elijah','Female','Lagos');
INSERT INTO Writer
VALUES (105,'David Hassan','Male','Abuja');
INSERT INTO Writer
VALUES (106,'Mara Disu','Female','Lagos');
INSERT INTO Writer
VALUES (108,'Joe Smith','Male','Lagos');
-- Query 
SELECT id,CHR(id) AS ch_id, name
FROM Writer
WHERE  CHR(ASCII(name)) = 'M';

Explanation

In the code above, we see the following:

  • Lines 1–7: We create a table named Writer, which has columns id, name, gender, and state.
  • Lines 11–21: We insert data into the Writer table.
  • Lines 24–26: We find the writer’s names that start with M and convert their id to a character using the CHR() function. Finally, we display the result.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)