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

The ORD() function can handle multi-byte characters. This is in contrast to the ASCII() function, which only handles single-byte (8 bit) characters.

Syntax

The syntax goes like this:

ORD(str)

Where str is the string argument.

ORD() will return the numeric code of only the leftmost character of this string.

If the leftmost character is a multi-byte character, ORD() uses the following formula to calculate its result:

  (1st byte code)
+ (2nd byte code x 256)
+ (3rd byte code x 256 x 256) ...

Example

Here’s a simple example to demonstrate:

SELECT ORD('H');

Result:

+----------+
| ORD('H') |
+----------+
|       72 |
+----------+

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

SELECT ORD('Homer');

Result:

+--------------+
| ORD('Homer') |
+--------------+
|           72 |
+--------------+

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

SELECT 
    ORD('H'),
    ORD('o'),
    ORD('m'),
    ORD('e'),
    ORD('r');

Result:

+----------+----------+----------+----------+----------+
| ORD('H') | ORD('o') | ORD('m') | ORD('e') | ORD('r') |
+----------+----------+----------+----------+----------+
|       72 |      111 |      109 |      101 |      114 |
+----------+----------+----------+----------+----------+

Multi-Byte Characters

Here’s an example that uses a multi-byte character:

SELECT ORD('©');

Result:

+-----------+
| ORD('©')  |
+-----------+
|     49833 |
+-----------+

Case Sensitivity

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

Example:

SELECT 
    ORD('H'),
    ORD('h');

Result:

+----------+----------+
| ORD('H') | ORD('h') |
+----------+----------+
|       72 |      104 |
+----------+----------+

A Database Example

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

USE PetHouse;
SELECT 
  PetName, 
  ORD(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 ORD() function to return the ASCII value for that character.

USE PetHouse;
SELECT 
  PetName,
  RIGHT(PetName, 1) 'Rightmost character',
  ORD(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 ORD('');

Result:

+---------+
| ORD('') |
+---------+
|       0 |
+---------+

Null Values

Providing null results in null being returned.

SELECT ORD(null);

Result:

+-----------+
| ORD(null) |
+-----------+
|      NULL |
+-----------+