MySQL - Split a field and insert result into columns

Question

I'm trying to split a field (at some delimiter, in the example below using ';') and insert the results of the split into a table. What I'm trying to do is illustrated in the tables below.

Note: There will never be more than 3 values delimited by the semicolon (;) in the FIRST row, but there can be fewer than 3.

mysql> SELECT * FROM new;
+-------+------+------+------+
| first | a    | b    | c    |
+-------+------+------+------+
| a;b;c | NULL | NULL | NULL |
| d;e;f | NULL | NULL | NULL |
| g;h   | NULL | NULL | NULL |
| i     | NULL | NULL | NULL |
+-------+------+------+------+

The result of the query should look like

mysql> SELECT * FROM new;
+-------+------+------+------+
| first | a    | b    | c    |
+-------+------+------+------+
| a;b;c | a    | b    | c    |
| d;e;f | d    | e    | f    |
| g;h   | g    | h    | NULL |
| i     | i    | NULL | NULL |
+-------+------+------+------+

The query that I've been trying to use is (this particular will retrieve the first value only).

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(first, ';', 1), ';', -1) FROM new;

However, this approach doesn't provide any help when the number of delimited characters is fewer than 3 as it will return the first character it encounters rather than NULL or the empty string ("").

Any one have any clues how to go about this?

Cheers

Will there be maximum three fields separated by comma in the first column always? — radar, Dec 04 '14 at 15:37

SQLChao · Accepted Answer · 2014-12-04T16:08:10.667

I used CASE WHEN to determine if there is 1 or more semicolon

SQL Fiddle Demo

SELECT 
  first,
  SUBSTRING_INDEX(first, ';', 1) AS 'a',
  CASE 
    WHEN LOCATE(';', first, 1) = 0 THEN NULL 
    ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(first, ';', 2), ';', -1)
  END AS 'b',
  CASE 
    WHEN LOCATE(';', first, 1) = 0 THEN NULL
    WHEN LOCATE(';', first, (LOCATE(';', first, 1) + 1)) = 0 THEN NULL
    ELSE SUBSTRING_INDEX(first, ';', -1)
  END AS 'c'
FROM new;

score 1 · Answer 2 · answered Dec 04 '14 at 18:59

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE new
    (`first` varchar(12))
;

INSERT INTO new
    (`first`)
VALUES
    ('ae;b;c'),
    ('d;ee;f'),
    ('g;he'),
    ('ie')
;

Query 1:

SELECT 
  first,
  SUBSTRING_INDEX(first, ';', 1) AS 'a',
  CASE WHEN first LIKE '%;%;%' THEN 
            SUBSTRING_INDEX(SUBSTRING_INDEX(first, ';', 2), ';', -1) 
       WHEN first LIKE '%;%' THEN 
            SUBSTRING_INDEX(first, ';', -1)
       ELSE NULL END AS 'b',
  CASE WHEN first LIKE '%;%;%' THEN
            SUBSTRING_INDEX(first, ';', -1)
       ELSE NULL END AS 'c'
FROM new

Results:

|  FIRST |  A |      B |      C |
|--------|----|--------|--------|
| ae;b;c | ae |      b |      c |
| d;ee;f |  d |     ee |      f |
|   g;he |  g |     he | (null) |
|     ie | ie | (null) | (null) |

MySQL - Split a field and insert result into columns

2 Answers2