SQL Server ASCII Function


By:

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
ascii function example

 

Show the List of ASCII Values of a String

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
UNICODE T-SQL code separated by commas

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]
UNICODE T-SQL code separated by commas

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.

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.

ascii query results

Related Articles


Last Update: 11/12/2021




Comments For This Article





download














get free sql tips
agree to terms