Get the numeric middle part by beginning with the ID, then stripping off the rightmost suffix : SUBSTRING « String « SQL / MySQL






  1. SQL / MySQL
  2. String
  3. SUBSTRING

Get the numeric middle part by beginning with the ID, then stripping off the rightmost suffix

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

mysql>
mysql> drop table housewares2;
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.Sort using combinations of substrings
9.Get 3-letter substrings from the dept column