SQL ASCII string function - how and when to use it with examples

Before we can understand what the ASCII function does in SQL we must understand what ASCII is.
You may have guessed by now that ASCII is an acronym. Its full title is American Standard Code for Information Interchange.
In a nutshell, ASCII is a character encoding language that computers use. Each character (such as a letter of the alphabet, symbol, number, etc.) is assigned a numeric code which is its ASCII code.
For example, the ASCII code for an upper case N character is 78.
What does the ASCII() function do in SQL?
The ASCII() function is used in SQL to return the numerical ASCII code for a character string that you pass to the function.
The parameter you pass to the function can be a single character or a multiple character string. If you pass in a string with multiple characters then the function returns the numeric ASCII code for the first character of that string.
Syntax
ASCII(character_expression)
The character_expression parameter can be of type char or varchar.
The function returns a value of type int.
Examples of using the ASCII function
Passing column values as the parameter
-- Creates a 'Customer' table with 2 columns.
CREATE TABLE Customer (FirstName varchar(20), LastName varchar(20));
-- Insert a row into the table with name of Nick Smith
INSERT INTO Customer (FirstName, LastName) VALUES ('Nick', 'Smith');
-- Query the ASCII code of the FirstName and LastName columns.
-- Because each column contains more than one character it is the
-- first character of the names ('N' and 'S') that will have their ASCII code displayed.
SELECT FirstName,
ASCII(FirstName) As FirstNameASCIICode,
LastName,
ASCII(LastName) As LastNameASCIICode
FROM Customer;
This returns the following result set
FirstName | FirstNameASCIICode | LastName | LastNameASCIICode |
---|---|---|---|
Nick | 78 | Smith | 83 |
Passing literal character strings to the ASCII() function
-- Create a table with a column for the character and a column for its ASCII code
CREATE TABLE ASCIIValues ( CharacterValue varchar(10), ASCIIValue int);
-- Insert some rows into the table for numbers, symbols and letters
INSERT INTO ASCIIValues (CharacterValue, ASCIIValue) VALUES ('!', ASCII('!'));
INSERT INTO ASCIIValues (CharacterValue, ASCIIValue) VALUES ('&', ASCII('&'))
INSERT INTO ASCIIValues (CharacterValue, ASCIIValue) VALUES ('7', ASCII('7'));
INSERT INTO ASCIIValues (CharacterValue, ASCIIValue) VALUES ('y', ASCII('y'));
-- select all rows
SELECT * FROM ASCIIValues;
This returns the following result set:
CharacterValue | ASCIIValue |
---|---|
! | 33 |
& | 38 |
7 | 55 |
y | 121 |
Bonus tips: the ASCII codes 0 - 31 represent non printable control codes. ASCII codes 32 - 127 represent printable characters including the lowercase and uppercase latin alphabet, numeric digits and punctuation. Codes 128 - 255 are 'extended' characters. This last set of characters can vary depending on the which variation of the ASCII table is being used.
We can write a loop in SQL to generate a table of ASCII codes and their equivalent character. To do this we can use the Char function to convert an ASCII code value to a character. The Char() function, therefore, performs the reverse functionality of the ASCII() function.
-- declare variable to use in the loop
DECLARE @i int = 0;
-- create a table variable to hold the list of codes
-- and their character equivalent
DECLARE @T TABLE (CharacterValue char(1), ASCIIValue int);
-- loop from 0 to 255
WHILE @i <= 255
BEGIN
INSERT INTO @T (CharacterValue,ASCIIValue)
VALUES (CHAR(@i), @i);
SET @i = @i + 1;
END
-- select all rows from the table variable
SELECT * FROM @T;
Here is part of the resultset that is generated by the above SQL WHILE loop:
Database Compatability
The ASCII() function can be used in SQL Server, Azure SQL, Azure SQL Data Warehouse and MySQL.