SQL ASCII Function

The SQL ASCII function returns the ASCII (American Standard Code for Information Interchange) code for the first character of a given string.

ASCII codes are numerical representations of characters, commonly used in computing.

SQL ASCII function is useful when you need to know the ASCII code of specific characters or compare characters based on their ASCII values.

In this tutorial, we will go through SQL ASCII String function, its syntax, and how to use this function in SQL statements, with the help of well detailed examples.

Syntax of SQL ASCII Function

The basic syntax of the SQL ASCII function is:

</>
Copy
SELECT ASCII('character_string') AS ascii_code;

Each part of this syntax has a specific purpose:

  • ASCII(‘character_string’): Returns the ASCII code of the first character in the specified string.
  • AS ascii_code: Assigns an alias to the result for easier reference in output.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the ASCII function in MySQL. Using MySQL 8.0 with MySQL Workbench, we will apply the ASCII function to different strings and evaluate their ASCII values.

Example 1: Getting the ASCII Code for a Single Character

To get the ASCII code for the letter “A”:

</>
Copy
SELECT ASCII('A') AS ascii_code;

This query returns 65, which is the ASCII code for the uppercase letter “A”.

SQL ASCII Function Example - Getting the ASCII Code for a Single Character

Example 2: Getting ASCII Codes for Lowercase and Special Characters

To retrieve the ASCII codes for the lowercase letter “a” and a special character “!” :

</>
Copy
SELECT ASCII('a') AS ascii_code_a, ASCII('!') AS ascii_code_exclamation;

This query returns 97 for “a” and 33 for “!”. ASCII values differ between uppercase and lowercase letters, and special characters each have unique codes.

SQL ASCII Function Example - Getting ASCII Codes for Lowercase and Special Characters

Example 3: Applying ASCII to a Multi-Character String

To find the ASCII code for the first character of the string “Hello”:

</>
Copy
SELECT ASCII('Hello') AS first_char_ascii;

This query returns 72, the ASCII code for the uppercase “H”, as ASCII only evaluates the first character.

SQL ASCII Function Example - Applying ASCII to a Multi-Character String

Example 4: Using ASCII in a Conditional Statement

To use ASCII to filter results based on ASCII values, let’s retrieve rows from a table where names start with an ASCII code greater than 75:

</>
Copy
SELECT name
FROM students
WHERE ASCII(name) > 75;

This query would return names starting with letters having ASCII values greater than 75, such as “L” or “M”.

Reference: SQL WHERE

SQL ASCII Function Example - Using ASCII in a Conditional Statement

FAQs for SQL ASCII Function

1. What does the SQL ASCII function do?

The ASCII function returns the ASCII code of the first character in a given string, representing it as a numerical value.

2. Can ASCII be used on multi-character strings?

Yes, but ASCII only evaluates the first character of a multi-character string.

3. Does ASCII return values for non-printable characters?

Yes, ASCII returns codes for non-printable characters, though their codes are less commonly used.

4. How does ASCII handle NULL values?

If NULL is passed to ASCII, it returns NULL as well.

5. What is the ASCII value of a space character?

The ASCII value of a space character is 32.