How ASCII() Works in MariaDB

In MariaDB, ASCII() is a built-in string function that returns the numeric ASCII value of the leftmost character of its string argument.

The ASCII() function only works on 8 bit characters. To get the code for multi-byte characters, use the ORD() function instead.

Syntax

The syntax goes like this:

ASCII(str)

Where str is the string argument.

ASCII() will return the numeric ASCII value of only the leftmost character of this string.

Example

Here’s a simple example to demonstrate:

SELECT ASCII('Maria');

Result:

+----------------+
| ASCII('Maria') |
+----------------+
|             77 |
+----------------+

This tells us that the uppercase letter M has the numeric ASCII value of 77.

As mentioned, ASCII() only returns the ASCII value of the leftmost character. Therefore, the following produces the same result:

SELECT ASCII('M');

Result:

+------------+
| ASCII('M') |
+------------+
|         77 |
+------------+

To demonstrate this further, let’s get the numeric ASCII value from each letter in the above string:

SELECT 
    ASCII('M'),
    ASCII('a'),
    ASCII('r'),
    ASCII('i'),
    ASCII('a');

Result:

+------------+------------+------------+------------+------------+
| ASCII('M') | ASCII('a') | ASCII('r') | ASCII('i') | ASCII('a') |
+------------+------------+------------+------------+------------+
|         77 |         97 |        114 |        105 |         97 |
+------------+------------+------------+------------+------------+

Case Sensitivity

Uppercase letters have a different ASCII value to their lowercase equivalents.

Example:

SELECT 
    ASCII('M'),
    ASCII('m');

Result:

+------------+------------+
| ASCII('M') | ASCII('m') |
+------------+------------+
|         77 |        109 |
+------------+------------+

A Database Example

Here’s an example of using ASCII() in a database query:

USE PetHouse;
SELECT 
  PetName, 
  ASCII(PetName) AS 'ASCII value of leftmost character'
FROM Pets;

Result:

+---------+-----------------------------------+
| PetName | ASCII value of leftmost character |
+---------+-----------------------------------+
| Fluffy  |                                70 |
| Fetch   |                                70 |
| Scratch |                                83 |
| Wag     |                                87 |
| Tweet   |                                84 |
| Fluffy  |                                70 |
| Bark    |                                66 |
| Meow    |                                77 |
+---------+-----------------------------------+

Rightmost Character

Here, I use the RIGHT() function to return the rightmost character from each pet name, and then use it again with the ASCII() function to return the ASCII value for that character.

USE PetHouse;
SELECT 
  PetName,
  RIGHT(PetName, 1) 'Rightmost character',
  ASCII(RIGHT(PetName, 1)) 'ASCII code'
FROM Pets;

Result:

+---------+---------------------+------------+
| PetName | Rightmost character | ASCII code |
+---------+---------------------+------------+
| Fluffy  | y                   |        121 |
| Fetch   | h                   |        104 |
| Scratch | h                   |        104 |
| Wag     | g                   |        103 |
| Tweet   | t                   |        116 |
| Fluffy  | y                   |        121 |
| Bark    | k                   |        107 |
| Meow    | w                   |        119 |
+---------+---------------------+------------+

Empty Strings

Providing an empty string results in 0 being returned.

SELECT ASCII('');

Result:

+-----------+
| ASCII('') |
+-----------+
|         0 |
+-----------+

Null Values

Providing null results in null being returned.

SELECT ASCII(null);

Result:

+-------------+
| ASCII(null) |
+-------------+
|        NULL |
+-------------+