Basic SQL - Learning MySQL - page 154

Note you can use the BINARY keyword in many places; for example, you can use it in
string comparisons. For example, searching for tracks with names alphabetically earlier
than the letter b returns 12 tracks:
mysql> SELECT track_name FROM track WHERE track_name < 'b';
+----------------------------------------------------+
| track_name |
+----------------------------------------------------+
| Ain't Gonna Rain Anymore |
| All Day Long |
| 1963 |
| Age Of Consent [Spectrum Arena, Warrington 1/3/86] |
| As It Is When It Was [Reading Festival 29/8/93] |
| Amandla |
| Age Of Consent |
| 5 8 6 |
| All Down The Line |
| Angel Dust |
| All Day Long |
| As It Is When It Was |
+----------------------------------------------------+
12 rows in set (0.00 sec)
However, if we specify that we want to perform the search in ASCII order, we get all
153 tracks, since they all start with an uppercase letter, and uppercase letters appear
before lowercase letters in the ASCII table:
mysql> SELECT track_name FROM track WHERE track_name < BINARY 'b';
+----------------------------------------------------------------------+
| track_name |
+----------------------------------------------------------------------+
| Do You Love Me? |
| Nobody's Baby Now |
| Loverman |
| Jangling Jack |
| Red Right Hand |
| I Let Love In |
...
| Broken Promise |
| As It Is When It Was |
| Weirdo |
| Paradise |
+----------------------------------------------------------------------+
153 rows in set (0.00 sec)
Sorting is performed as appropriate to the column type. For example, if you're sorting
dates, it organizes the rows in ascending date order. You can force the sort to behave
differently, using the CAST( ) function and the AS keyword. Suppose, for example, you
want to sort the track table by ascending time , but you want the times to be treated as
strings. Here's how you do it:
mysql> SELECT time, track_name FROM track ORDER BY CAST(time AS CHAR);
+-------+-----------------------------------------------------+
| time | track_name |