Use FIELD( ) with column substrings : FIELD « Function « SQL / MySQL

1. SQL / MySQL
2. Function
3. FIELD

Use FIELD( ) with column 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>
mysql> SELECT id, description FROM housewares
    -> ORDER BY FIELD(RIGHT(id,2),'US','UK','JP','SG');
+------------+------------------+
| id         | description      |
+------------+------------------+
| DIN40672US | dining table     |
| BTH00485US | shower stall     |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven   |
| BTH00415JP | lavatory         |
| BED00038SG | bedside lamp     |
+------------+------------------+
6 rows in set (0.00 sec)

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

   
    
    
    
    
  

Related examples in the same category