How to use the MySQL ORD() function

In this article, we will learn how to use the MySQL ORD() function, which returns the numeric value of the leftmost character of a string. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with strings and characters.

Syntax

The syntax of the ORD() function is as follows:

ORD(str)

The str parameter can be any string expression. If the str parameter is NULL or an empty string, the function returns 0. If the str parameter is a multibyte character, the function returns the numeric value of the first byte of the character.

Examples

Let’s see some examples of how to use the ORD() function in MySQL.

Example 1: Get the numeric value of a single character

We can use the ORD() function to get the numeric value of a single character, according to the ASCII table. For example:

SELECT ORD('A') AS ord_A, ORD('a') AS ord_a, ORD('0') AS ord_0, ORD('!') AS ord_exclamation;

This query will return the numeric value of the characters ‘A’, ‘a’, ‘0’, and ‘!’. The query will return something like this:

Example 2: Get the numeric value of the first character of a string

We can use the ORD() function to get the numeric value of the first character of a string. For example:

SELECT ORD('Hello') AS ord_Hello, ORD('World') AS ord_World, ORD('123') AS ord_123, ORD('') AS ord_empty;

This query will return the numeric value of the first character of the strings ‘Hello’, ‘World’, ‘123’, and ‘’. The query will return something like this:

Example 3: Get the numeric value of a multibyte character

We can use the ORD() function to get the numeric value of the first byte of a multibyte character, such as a Chinese character. For example:

SELECT ORD('你') AS ord_ni, ORD('好') AS ord_hao, ORD('世') AS ord_shi, ORD('界') AS ord_jie;

This query will return the numeric value of the first byte of the Chinese characters ‘你’, ‘好’, ‘世’, and ‘界’. The query will return something like this:

Related Functions

There are some other functions that are related to the ORD() function, and can be useful for working with strings and characters. Here are some of them:

• CHAR(): This function returns the character corresponding to a numeric value, according to the ASCII table. For example, CHAR(65) returns ‘A’.
• ASCII(): This function returns the numeric value of the first character of a string, similar to the ORD() function. However, the ASCII() function only works for single-byte characters, while the ORD() function can handle multibyte characters. For example, ASCII('A') returns 65, but ASCII('你') returns 0.
• CHAR_LENGTH(): This function returns the number of characters in a string. For example, CHAR_LENGTH('Hello') returns 5.
• LENGTH(): This function returns the number of bytes in a string. For example, LENGTH('Hello') returns 5, but LENGTH('你好') returns 6.
• SUBSTRING(): This function returns a substring of a string, starting from a specified position and with a specified length. For example, SUBSTRING('Hello', 2, 3) returns ’ell’.

Conclusion

In this article, we learned how to use the MySQL ORD() function, which returns the numeric value of the leftmost character of a string. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with strings and characters.