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

Keyboard characters that have an equivalent numeric code that can be found by using SQL ASCII

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:

ASCII Codes and their equivalent characters

Database Compatability

The ASCII() function can be used in SQL Server, Azure SQL, Azure SQL Data Warehouse and MySQL.