SQL - ASCII() Function

The SQL ASCII() function returns the decimal representation of the first character in a character string based on its code point in the ASCII character set.

The ASCII function takes one argument of any character data type. Returns an integer value, based on the first character of the argument.

If the argument is NULL or if the argument is an empty string, the ASCII function returns NULL.We can also use the ASCII function with string fields in an SQL table.

Note − For upper case alphabets(A to Z) the ASCII values = 65 to 90. For lower case alphabets(a to z) the ASCII values = 97 to 122.

Following is the syntax of the SQL ASCII() function −

SELECT ASCII (expr);

Parameters

  • expr − It gives a string or character value.

Return value

It returns the ASCII value of a Character or left most character in a string.

Example

The following SELECT query displays the SQL ASCII value of the first character of the given string.

SELECT ASCII ("SQL stands for Structured Query Language") AS ASCII_S;

Output

Following is the output of the above query −

+---------+
| ASCII_S |
+---------+
|      83 |
+---------+

In the above data the string is "SQL stands for Structured Query Language", the first character is upper case “S” and the ASCII value of that alphabet is 83. So the Query displayed the same value.

Example

The following SELECT query displays the ASCII value of the string we have given.

select ASCII ("Kaushik");

Output

The above SQL query produces the following output −

+-------------------+
| ASCII ("Kaushik") |
+-------------------+
|                75 |
+-------------------+

In above we know ASCII means the representation of the first character. So, even though if we are not the specifying character of the string. It will display the output of the string character as the ASCII values.

Example

The following SELECT query shows the ASCII value of the 'B' character.

SELECT ASCII("B");

Output

On executing the above query, it will produce the following output −

+------------+
| ASCII("B") |
+------------+
|         66 |
+------------+

In above Query, we have directly passed the character for the ASCII value.

Example

You can pass the table column as an argument to the ASCII() function to convert the character or string into a ASCII Value. Assume we have created a table with the name Customers using the CREATE statement as follows −

create table CUSTOMERS(
   ID INT NOT NULL, 
   NAME VARCHAR(15) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR(25), 
   SALARY DECIMAL(10, 4), PRIMARY KEY(ID)
);

Now let's insert seven records into the customers table using the INSERT statement as follows:−

insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

The following Query shows the ASCII value of the first or leftmost character in the NAME column from the table. −

SELECT NAME, ASCII(NAME) As ASCIIvalue from CUSTOMERS;

Output

After executing the above statement, it produces the following output −

+----------+------------+
| NAME     | ASCIIvalue |
+----------+------------+
| Ramesh   |         82 |
| Khilan   |         75 |
| kaushik  |        107 |
| Chaitali |         67 |
| Hardik   |         72 |
| Komal    |         75 |
| Muffy    |         77 |
+----------+------------+

Example

The following SELECT query uses the ASCII function with the NAME, and ADDRESS columns of those CUSTOMERS whose AGE is greater than 24 in the above CUSTOMERS table.

SELECT ID, NAME, ASCII(NAME), ADDRESS, ASCII(ADDRESS) FROM CUSTOMERS WHERE AGE > 24;

Output

After executing the above statement, it produces the following output −

+----+----------+-------------+-----------+----------------+
| ID | NAME     | ASCII(NAME) | ADDRESS   | ASCII(ADDRESS) |
+----+----------+-------------+-----------+----------------+
|  1 | Ramesh   |          82 | Ahmedabad |             65 |
|  2 | Khilan   |          75 | Delhi     |             68 |
|  4 | Chaitali |          67 | Mumbai    |             77 |
|  5 | Hardik   |          72 | Bhopal    |             66 |
+----+----------+-------------+-----------+----------------+
sql-string-functions.htm
Advertisements