Get the second and fourth segments from the id values: : SUBSTRING_INDEX « String « SQL / MySQL






  1. SQL / MySQL
  2. String
  3. SUBSTRING_INDEX

Get the second and fourth segments from the id values:

       
mysql>
mysql> CREATE TABLE housewares3
    -> (
    ->  id                      VARCHAR(20),
    ->  description     VARCHAR(255)
    -> );
mysql>
mysql> INSERT INTO housewares3 (id,description)
    ->  VALUES
    ->          ('13-478-92-2', 'dining table'),
    ->          ('873-48-649-63', 'garbage disposal'),
    ->          ('8-4-2-1', 'microwave oven'),
    ->          ('97-681-37-66', 'bedside lamp'),
    ->          ('27-48-534-2', 'shower stall'),
    ->          ('5764-56-89-72', 'lavatory')
    -> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM housewares3;
+---------------+------------------+
| id            | description      |
+---------------+------------------+
| 13-478-92-2   | dining table     |
| 873-48-649-63 | garbage disposal |
| 8-4-2-1       | microwave oven   |
| 97-681-37-66  | bedside lamp     |
| 27-48-534-2   | shower stall     |
| 5764-56-89-72 | lavatory         |
| 13-478-92-2   | dining table     |
| 873-48-649-63 | garbage disposal |
| 8-4-2-1       | microwave oven   |
| 97-681-37-66  | bedside lamp     |
| 27-48-534-2   | shower stall     |
| 5764-56-89-72 | lavatory         |
| 13-478-92-2   | dining table     |
| 873-48-649-63 | garbage disposal |
| 8-4-2-1       | microwave oven   |
| 97-681-37-66  | bedside lamp     |
| 27-48-534-2   | shower stall     |
| 5764-56-89-72 | lavatory         |
+---------------+------------------+
18 rows in set (0.00 sec)

mysql>
mysql> SELECT
    -> id,
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1) AS segment2,
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',4),'-',-1) AS segment4
    -> FROM housewares3;
+---------------+----------+----------+
| id            | segment2 | segment4 |
+---------------+----------+----------+
| 13-478-92-2   | 478      | 2        |
| 873-48-649-63 | 48       | 63       |
| 8-4-2-1       | 4        | 1        |
| 97-681-37-66  | 681      | 66       |
| 27-48-534-2   | 48       | 2        |
| 5764-56-89-72 | 56       | 72       |
| 13-478-92-2   | 478      | 2        |
| 873-48-649-63 | 48       | 63       |
| 8-4-2-1       | 4        | 1        |
| 97-681-37-66  | 681      | 66       |
| 27-48-534-2   | 48       | 2        |
| 5764-56-89-72 | 56       | 72       |
| 13-478-92-2   | 478      | 2        |
| 873-48-649-63 | 48       | 63       |
| 8-4-2-1       | 4        | 1        |
| 97-681-37-66  | 681      | 66       |
| 27-48-534-2   | 48       | 2        |
| 5764-56-89-72 | 56       | 72       |
+---------------+----------+----------+
18 rows in set (0.00 sec)

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








Related examples in the same category

1.To return everything to the right or left of a given character, use SUBSTRING_INDEX(str,c,n).
2.Prepend a sufficient number of periods to the hostname values to guarantee that they have the requisite number