Sorting by Fixed-Length Substrings : Mid « String « SQL / MySQL

1. SQL / MySQL
2. String
3. Mid

Sorting by Fixed-Length Substrings

      
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>
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 |
+------------+----------+--------+---------+
| DIN40672US | DIN      | 40672  | US      |
| KIT00372UK | KIT      | 00372  | UK      |
| KIT01729JP | KIT      | 01729  | JP      |
| BED00038SG | BED      | 00038  | SG      |
| BTH00485US | BTH      | 00485  | US      |
| BTH00415JP | BTH      | 00415  | JP      |
+------------+----------+--------+---------+
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