Use and analysis of mysqlORD () and ASCII () functions

Both mysqlORD () and ASCII () functions are used to convert characters into ascii values. This article introduces mysqlORD () and ASCII () functions to coders through examples () for detailed analysis of functions, refer. ASCII (str1)

Returns the ASCII code value of the leftmost character of the str string. If str is a null string, 0 is returned. If 'str' is NULL, return NULL

Example:

1.

mysql> select ascii('hi');+————-+| ascii('hi') |+————-+|         104 |+————-+1 row in set

104 is the ASCII value of h

2. output ASCII values of B and B

mysql> SELECT ASCII('b')AS Lower_Case, ASCII('B') AS Upper_Case;+————+————+| Lower_Case | Upper_Case |+————+————+|         98 |         66 |+————+————+1 row in set

3. use ASCII functions in the where statement

Output data whose ASCII value is less than 70 at the beginning of aut_name

SELECT aut_name,ASCII(aut_name)as "ASCII value of 1st character"   FROM author   WHERE ASCII(aut_name)<70;

4. the output field does not contain any data without ASCII values.

SELECT * FROM table_name WHERE NOT column_to_check REGEXP '[A-Za-z0-9.,-]';

5. use it with SUBSTRING to calculate the second ASCII value after the string

mysql> select ASCII(SUBSTRING('database',2,1));+———————————-+| ASCII(SUBSTRING('database',2,1)) |+———————————-+|                               97 |+———————————-+1 row in set

ORD () function

The ORD () function returns the ASCII value of the first character of the string.

Syntax: ORD (string)

Here are some simple examples:

mysql> SELECT ORD('i');+----------+| ORD('i') |+----------+|      105 |+----------+1 row in set

Or:

mysql> SELECT ORD('NowaMagic');+------------------+| ORD('NowaMagic') |+------------------+|               78 |+------------------+1 row in set

What if the Chinese character is used?

Mysql> select ord ('Concise modern magine'); + ------------------- + | ORD ('Concise modern magine ') | + --------------------- + | 15183488 | + --------------------- + 1 row in setmysql> select ord (' '); + ----------- + | ORD (' ') | + ----------- + | 15183488 | + ----------- + 1 row in set

Why is there an 8-digit length? The reason is that the character set used by the database is problematic, where the database uses a UTF-8 with 16 bits representing a symbol. By the way, paste the SQL statement:

mysql> SHOW CHARACTER SET;+----------+-----------------------------+---------------------+--------+| Charset  | Description                 | Default collation   | Maxlen |+----------+-----------------------------+---------------------+--------+| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 || dec8     | DEC West European           | dec8_swedish_ci     |      1 || cp850    | DOS West European           | cp850_general_ci    |      1 || hp8      | HP West European            | hp8_english_ci      |      1 || koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 || latin1   | cp1252 West European        | latin1_swedish_ci   |      1 || latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 || swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 || ascii    | US ASCII                    | ascii_general_ci    |      1 || ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 || sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 || hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 || tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 || euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 || koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 || gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 || greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 || cp1250   | Windows Central European    | cp1250_general_ci   |      1 || gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 || latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 || armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 || utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 || ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 || cp866    | DOS Russian                 | cp866_general_ci    |      1 || keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 || macce    | Mac Central European        | macce_general_ci    |      1 || macroman | Mac West European           | macroman_general_ci |      1 || cp852    | DOS Central European        | cp852_general_ci    |      1 || latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 || utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 || cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 || utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 || cp1256   | Windows Arabic              | cp1256_general_ci   |      1 || cp1257   | Windows Baltic              | cp1257_general_ci   |      1 || utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 || binary   | Binary pseudo charset       | binary              |      1 || geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 || cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 || eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |+----------+-----------------------------+---------------------+--------+39 rows in set