Database Reference
In-Depth Information
Solution
Pull out the parts you need with
LEFT()
,
MID()
, or
RIGHT()
, and sort them.
Discussion
Suppose that a
housewares
table catalogs houseware furnishings, each identified by 10-
character ID values consisting of three subparts: a three-character category abbreviation
(such as
DIN
for “dining room” or
KIT
for “kitchen”), a five-digit serial number, and a
two-character country code indicating where the part is manufactured:
mysql>
SELECT * FROM housewares;
+------------+------------------+
| id | description |
+------------+------------------+
| DIN40672US | dining table |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven |
| BED00038SG | bedside lamp |
| BTH00485US | shower stall |
| BTH00415JP | lavatory |
+------------+------------------+
This is not necessarily a good way to store complex ID values, and later we'll consider
how to represent them using separate columns. For now, assume that the values must
be stored as shown.
To sort rows from this table based on the
id
values, use the entire column value:
mysql>
SELECT * FROM housewares ORDER BY id;
+------------+------------------+
| id | description |
+------------+------------------+
| BED00038SG | bedside lamp |
| BTH00415JP | lavatory |
| BTH00485US | shower stall |
| DIN40672US | dining table |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven |
+------------+------------------+
But you might also have a need to sort on any of the three subparts (for example, to sort
by country of manufacture). For that kind of operation, functions such as
LEFT()
,
MID()
, and
RIGHT()
are useful to extract
id
value components:
mysql>
SELECT id,
->
LEFT(id,3) AS category,
->
MID(id,4,5) AS serial,
->
RIGHT(id,2) AS country
->
FROM housewares;
+------------+----------+--------+---------+
| id | category | serial | country |
