By: Greg Robidoux
The ASCII function is used to return the ASCII value for the first character in a string.
Syntax
ASCII(character)
Parameters
- character - this is a valid character or number within the ASCII values
Simple ASCII Example
If we run the following code:
SELECT ASCII(1)
SELECT ASCII(2)
SELECT ASCII('3')
SELECT ASCII('A')
SELECT ASCII('B')
SELECT ASCII('C')
We get the following values, which correspond to the ASCII values for the characters 1,2,3,A,B,C:
49 50 51 65 66 67
Get ASCII Value from Variable
We can also create a variable to hold a string and then get the ASCII value.
DECLARE @string nvarchar(10) = 'T' SELECT ASCII(@string)
The ASCII value "T" is 84.
If we run the following for the word "Test".
DECLARE @string nvarchar(10) = 'Test' SELECT ASCII(@string)
The result is still 84, because the ASCII function just uses the first character in the string.
Get ASCII Value from Table Column
In this example, we use the ASCII function on a column from a table.
SELECT TOP 5 name, ASCII(name) as ASCIIvalue FROM Authors
Show the List of ASCII Values of a String
-- www.mssqltips.com
-- https://www.mssqltips.com/sqlservertutorial/9347/sql-ascii-function/
CREATE FUNCTION showASCII(@string VARCHAR(100))
returns varchar(100)
AS
BEGIN
DECLARE @length smallint = LEN(@string)
DECLARE @position smallint = 0
DECLARE @codes varchar(max) = ''
WHILE @length >= @position
BEGIN
SELECT @codes = @codes + CONCAT(ASCII(SUBSTRING(@string,@position,1)),',')
SELECT @position = @position + 1
END
SELECT @codes = SUBSTRING(@codes,2,LEN(@codes)-2)
RETURN @codes
END
Next, we will execute the function.
SELECT dbo.showASCII('hello world') as codes
Here is how we can use this function to get the data from a table.
SELECT FirstName, dbo.showASCII(FirstName) as codes FROM [Person].[Person]
Script to List All Characters and ASCII values
To get a list of all of the character and ASCII values, we could use the following script. Note this script uses the CHAR function.
-- www.mssqltips.com
-- https://www.mssqltips.com/sqlservertutorial/9347/sql-ascii-function/
DECLARE @counter INT = 0
CREATE TABLE #AsciiValues ([char] nchar(1), [value] int)
WHILE (@counter <= 255 )
BEGIN
BEGIN TRY
INSERT INTO #AsciiValues
SELECT CHAR(@counter), @counter
SET @counter = @counter + 1
END TRY
BEGIN CATCH;
SET @counter = @counter + 1
IF @counter > 255
BEGIN
BREAK
END
END CATCH
END
SELECT * FROM #AsciiValues
DROP TABLE #AsciiValues
Here are the first few rows from the query.
Related Articles
- SQL Server Char Function and Reference Guide - this includes a table of values
- SQL CHAR
Last Update: 2/6/2023
