MySQL in a Nutshell, 2nd Edition

Name

ASCII()

Synopsis

ASCII(string)

This function returns the numeric code corresponding to the first character of a given string. If the given string is empty, 0 is returned. Despite the function’s name, it works for characters outside the ASCII set (that is, characters that correspond to values above 127) and is probably most useful for such characters.

As an example of this function’s use, suppose that for a college we had a table listing the names of fraternities with their Greek letters. For easier manipulation of the data contained in a column, we might want to convert the Greek letters to a numeric code with this function:

SELECT greek_id,
CONCAT_WS('-',
   ASCII( SUBSTR(greek_id, 1, 1) ),
   ASCII( SUBSTR(greek_id, 2, 1) ),
   ASCII( SUBSTR(greek_id, 3, 1) )
) AS 'ASCII Values'
FROM fraternities WHERE frat_id = 101;

+----------+--------------+
| greek_id | ASCII Values |
+----------+--------------+
|  Δ Σ Π      |  196-211-208   |
+----------+--------------+

In this example, we use the SUBSTR() function to extract each letter so we can then convert each one individually to its numeric equivalent with the ASCII() function. Then, using CONCAT_WS(), we insert hyphens between each number returned. We can use this number to more easily manage the data related to this fraternity. See the descriptions of CHAR() and CONVERT() later in this chapter for more information on this function and for more details related to this example.