Sorting by Variable-Length Substrings : SUBSTRING « String « SQL / MySQL






  1. SQL / MySQL
  2. String
  3. SUBSTRING

Sorting by Variable-Length Substrings

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

mysql>
mysql> INSERT INTO housewares2 (id,description)
    ->  VALUES
    ->          ('DIN40672US', 'dining table'),
    ->          ('KIT372UK', 'garbage disposal'),
    ->          ('KIT1729JP', 'microwave oven'),
    ->          ('BED38SG', 'bedside lamp'),
    ->          ('BTH485US', 'shower stall'),
    ->          ('BTH415JP', 'lavatory')
    -> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM housewares2;
+------------+------------------+
| id         | description      |
+------------+------------------+
| DIN40672US | dining table     |
| KIT372UK   | garbage disposal |
| KIT1729JP  | microwave oven   |
| BED38SG    | bedside lamp     |
| BTH485US   | shower stall     |
| BTH415JP   | lavatory         |
+------------+------------------+
6 rows in set (0.00 sec)

mysql>
mysql> SELECT id, SUBSTRING(id,4) FROM housewares2;
+------------+-----------------+
| id         | SUBSTRING(id,4) |
+------------+-----------------+
| DIN40672US | 40672US         |
| KIT372UK   | 372UK           |
| KIT1729JP  | 1729JP          |
| BED38SG    | 38SG            |
| BTH485US   | 485US           |
| BTH415JP   | 415JP           |
+------------+-----------------+
6 rows in set (0.00 sec)

mysql>








Related examples in the same category

1.SUBSTRING(, )
2.SUBSTRING(, , )
3.Create Initial name with substr function
4.In some cases, pattern matches are equivalent to substring comparisons.
5.SUBSTRING( ) function takes a string and a starting position, returning everything to the right of the positio
6.Substrings can be used to perform comparisons.
7.Sort using combinations of substrings
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