Sort using combinations of substrings

      
mysql>
mysql> CREATE TABLE housewares
    -> (
    ->  id                      VARCHAR(20),
    ->  description     VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO housewares (id,description)
    ->  VALUES
    ->          ('DIN40672US', 'dining table'),
    ->          ('KIT00372UK', 'garbage disposal'),
    ->          ('KIT01729JP', 'microwave oven'),
    ->          ('BED00038SG', 'bedside lamp'),
    ->          ('BTH00485US', 'shower stall'),
    ->          ('BTH00415JP', 'lavatory')
    -> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM housewares;
+------------+------------------+
| id         | description      |
+------------+------------------+
| DIN40672US | dining table     |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven   |
| BED00038SG | bedside lamp     |
| BTH00485US | shower stall     |
| BTH00415JP | lavatory         |
+------------+------------------+
6 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM housewares ORDER BY RIGHT(id,2), MID(id,4,5);
+------------+------------------+
| id         | description      |
+------------+------------------+
| BTH00415JP | lavatory         |
| KIT01729JP | microwave oven   |
| BED00038SG | bedside lamp     |
| KIT00372UK | garbage disposal |
| BTH00485US | shower stall     |
| DIN40672US | dining table     |
+------------+------------------+
6 rows in set (0.00 sec)

mysql>
mysql> drop table housewares;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	SUBSTRING(, )
2.	SUBSTRING(, , )
3.	Sorting by Variable-Length Substrings
4.	Create Initial name with substr function
5.	In some cases, pattern matches are equivalent to substring comparisons.
6.	SUBSTRING( ) function takes a string and a starting position, returning everything to the right of the positio
7.	Substrings can be used to perform comparisons.
8.	Get 3-letter substrings from the dept column
9.	Get the numeric middle part by beginning with the ID, then stripping off the rightmost suffix

Sort using combinations of substrings : SUBSTRING « String « SQL / MySQL

Related examples in the same category