To sort rows by product serial number, use MID( ) to extract the middle five characters from the id values, be : MID « Math « SQL / MySQL

1. SQL / MySQL
2. Math
3. MID

To sort rows by product serial number, use MID( ) to extract the middle five characters from the id values, be

     
ginning with the fourth
mysql>
mysql> CREATE TABLE housewares
    -> (
    ->  id                      VARCHAR(20),
    ->  description     VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.00 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 MID(id,4,5);
+------------+------------------+
| id         | description      |
+------------+------------------+
| BED00038SG | bedside lamp     |
| KIT00372UK | garbage disposal |
| BTH00415JP | lavatory         |
| BTH00485US | shower stall     |
| KIT01729JP | microwave oven   |
| 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