String Functions - Learning MySQL and MariaDB - page 231

MySQL will sort the results based on their rendered values and not their column values.
Here's how that would look:
SELECT bird_name, gender_age, bird_image
FROM bird_images
WHERE bird_name LIKE '%Plover%'
ORDER BY CONVERT(gender_age, CHAR)
LIMIT 5;
+-----------------+------------+----------------------------+
| bird_name | gender_age | bird_image |
+-----------------+------------+----------------------------+
| Wilson's Plover | female | wilson_plover_female.jpg |
| Snowy Plover | female | snowy_plover_female.jpg |
| Wilson's Plover | juvenile | wilson_plover_juvenile.jpg |
| Snowy Plover | juvenile | snowy_plover_juvenile.jpg |
| Wilson's Plover | male | wilson_plover_male.jpg |
+-----------------+------------+----------------------------+
Notice that for the CONVERT() function, a comma is used to separate the string given
from the data type instead of the AS keyword. The data type given as the second argument
canbe BINARY , CHAR , DATE , DATETIME , SIGNED [INTEGER] , TIME , or
UNSIGNED [INTEGER] . BINARY converts a string to a binary string. You can add also
CHARACTER SET to use a different character set from the default for the value given. To
convert the character set of a given string to another, you have to use the USING option,
likeso:
SELECT bird_name , gender_age , bird_image
FROM bird_images
WHERE bird_name LIKE '%Plover%'
ORDER BY CONVERT ( gender_age USING utf8 )
LIMIT 5 ;