SQL ASCII Function

The SQL ASCII function is one of the SQL String Functions, which is used to return the ASCII code of the leftmost character of a character expression. The syntax of the SQL Server ASCII Function is

SELECT ASCII (Character_Expression)
FROM [Source]

Character_Expression: Please specify the valid Expression for which you want to find the ASCII code. ASCII Function will return the ASCII code of the leftmost character of this expression. This Character_Expression can be a Char or Varchar.

Return Type

This function will return INT

SQL ASCII Function Example 1

The SQL Server ASCII Function returns the ASCII code of the leftmost character of the given expression. The following query will show multiple ways to use this ASCII function.

TIP: Please refer ASCII Table to check the character codes of each character.

DECLARE @i VARCHAR(25)
DECLARE @j INT

-- Initialize the variables.  
SET @i = 'a'
SET @j = 1

SELECT ASCII(@i) AS Result1,
	ASCII(@j) AS Result2 ;

SELECT ASCII('efgh') AS Result3;  

SELECT ASCII('789') AS Result4;

OUTPUT

ANALYSIS

From the below statement, you can see that we are finding the ASCII code of alphabet ‘a’ and integer 1. We also assigned new names to the result as ‘Result1’, and ‘Result2’ using ALIAS Column in SQL Server.

SELECT ASCII(@i) AS Result1,
       ASCII(@j) AS Result2 ;

In the next line, We used the SQL ASCII function directly on a group of characters (word). Here, ASCII function will return leftmost character (i.e., e) and that should be 101

SELECT ASCII('efgh') AS Result3;

As you can see from the below statement, we used the ASCII function directly on integer. Here, ASCII String Functions will return leftmost character (i.e., 7) and that should be 55

SELECT ASCII('789') AS Result4;

ASCII Function Example 2

In this example, We are going to use the SQL ASCII function inside the WHILE LOOP. I suggest you refer both the SUBSTRING and WHILE LOOP articles to understand the query execution.

DECLARE @i int, 
        @str char(16);  

-- Initialize the variables.  
SET @i = 1;  
SET @str = 'Tutorial Gateway'; 
 
WHILE @i <= DATALENGTH(@str)
	BEGIN
		SELECT SUBSTRING(@str, @i, 1) AS [Char_Value],
			ASCII(SUBSTRING(@str, @i, 1)) AS [ASCII_Result]
		SET @i = @i + 1
	END;

OUTPUT

ASCII Function Example 3

In this example, we are going to implement the SQL Server ASCII function on the column. For this, We are going to use the below-shown data

The following statement will return the ASCII code of leftmost character in [FirstName] column and [LastName] column and ASCII code of leftmost number in [YearlyIncome] column.

SELECT [FirstName] 
      ,ASCII([FirstName]) AS [FN_ASCII] 
      ,[LastName]
      ,ASCII([LastName]) AS [LN_ASCII] 
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ASCII([YearlyIncome]) AS [YI_ASCII]
      ,[Sales]
  FROM [SQL Tutorial].[dbo].[Employee]

From the below screenshot you can observe that, 74 is the ASCII code of J, 89 is the ASCII code for Y, and 57 is the ASCII code for 9