The SQL ASCII is one of the SQL String Function, which is used to return the ASCII code of the left most character of a character expression. In this article we will show you, How to use/write ASCII in SQL Server 2014 with example.
SQL ASCII Function Syntax
The basic syntax of the SQL Server ASCII Function is as shown below:
|
1 2 |
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 left most character of this expression. This Character_Expression can be a Char, or Varchar.
TIP: Please refer ASCII Table to check the character codes of each and every character.
Return Type
This function will return INT
SQL ASCII Function Example 1
The ASCII Function is used to return the ASCII code of the leftmost character of the given expression. The following query will show multiple ways to use this function.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
Below lines of code is used to declare two variables of type VARCHAR and Integer type. Next, we assigning the string data ‘a’ and 1.
|
1 2 3 4 5 6 |
DECLARE @i VARCHAR(25) DECLARE @j INT -- Initialize the variables. SET @i = 'a' SET @j = 1 |
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.
|
1 2 |
SELECT ASCII(@i) AS Result1, ASCII(@j) AS Result2 ; |
In the next line, We used the ASCII function directly on group of characters (word). Here, ASCII function will return leftmost character (i.e., e) and that should be 101
|
1 |
SELECT ASCII('efgh') AS Result3; |
You can see from the below statement, We used the ASCII function directly on integer. Here, ASCII function will return leftmost character (i.e., 7) and that should be 55
|
1 |
SELECT ASCII('789') AS Result4; |
SQL ASCII Function Example 2
In this example, We are going to use the ASCII function inside the WHILE LOOP. I suggest you to refer both the SUBSTRING and WHILE LOOP articles to understand the query execution.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
SQL ASCII Function Example 3
In this example, we are going to implement the ASCII function on the column. For this, We are going to use the below shown data
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.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 |
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] |
OUTPUT
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
Thank You for Visiting Our Blog
Leave a Reply