String Functions - Learning MySQL and MariaDB - page 229

Converting String Types
There may betimes when you will have to work with tables created by people who might
not have made the best choices for column data types. Sometimes you can alter the tables,
but sometimes you may not be allowed to do so. For manipulating data from such tables or
for importing data from them, you can usethe CAST() or CONVERT() functions to
change the data type of columns. The effect just takes place within your SQL statement, not
the database itself. Let's look at some examples of how and why you might use these two
functions, which are basically synonymous except for a minor syntax difference.
Suppose we're given a table containing images of birds in a particular area, showing fe-
male, male, and juvenile color patterns. One of the columns contains numbers for ordering
birds based loosely on the type of bird and the date when usually seen in the area. This
column isn't a numeric data type like INT , but is CHAR . When we sort the data based on
this column, MySQL will sort the rows lexically, not numerically. Here's an example of
how that might look:
SELECT sorting_id, bird_name, bird_image
FROM bird_images
ORDER BY sorting_id
LIMIT 5;
+------------+-----------------+----------------------------+
| sorting_id | bird_name | bird_image |
+------------+-----------------+----------------------------+
| 11 | Arctic Loon | artic_loon_male.jpg |
| 111 | Wilson's Plover | wilson_plover_male.jpg |
| 112 | Wilson's Plover | wilson_plover_female.jpg |
| 113 | Wilson's Plover | wilson_plover_juvenile.jpg |
| 12 | Pacific Loon | pacific_loon_male.jpg |
+------------+-----------------+----------------------------+
Notice that the rows with a sorting_id starting with 11 n are listed before one with the
value of 12. That's because MySQL is reading the data as characters and not numbers. The
two Loons should be together, before the Plovers are listed.
We can use the CAST() function to cast the values taken from sorting_id into the
INT data type:
SELECT sorting_id, bird_name, bird_image
FROM bird_images ORDER BY CAST(sorting_id AS INT) LIMIT 5;
+------------+-----------------+----------------------------+
| sorting_id | bird_name | bird_image |