Split if field contains delimiter and preserve related row values MySQL query

Question

The schema for the table is :

CREATE TABLE `authors` (
  `authorid` int(10) NOT NULL AUTO_INCREMENT,
  `emailaddress` varchar(255) DEFAULT NULL,
  'send' int(1) DEFAULT '0',
  `status` int(1) DEFAULT '0',
  PRIMARY KEY (`authorid`),
  UNIQUE KEY `authorid` (`authorid`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

Sample data :

 123    john@smith.com;sue@test.org                    1
5271    sally@john.doe                                 0
 834    jacob@tom.smith;foo@bar.net; big@data.cow      1
  27    tuesday@rubys.eat                              1
1977    (null)                                         0
 224                                                   1
  88    miles@per.hour                                 1

Note : (null) above is not the text null, but an actual null value

What I would like, is a query that can get the data from the second column and return a result set as follows :

  id email
------------------------
 123 john@smith.com
 123 sue@test.org
5271 sally@john.doe
 834 jacob@tom.smith
 834 foo@bar.net
 834 big@data.cow
  27 ruby@tuesdays.eat
  88 miles@per.hour

I am looking for something more generic like this example for SQL Server

SELECT A.[State],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
FROM  (SELECT [State],  
         CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

Which does exactly what I want to do, but I want to do it in MySQL (as you can see from the picture a demonstration of what the above SQL Server query does)

I firmly believe that when provided with a way to do something in a language, it stands to reason there must be a way to UNDO that very same thing. In MySQL, you can pivot data one way with GROUP_CONCAT, so there must be some what to ungroup data.

http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — Gurwinder Singh, Dec 25 '16 at 19:30
@GurwinderSingh - if you are talking about `(null)` vs blanks, that is something due to a glitch in one of the applications (3rd party) that hooks into it. My code will store as null or `;` delimited proper, and where single -- no delimiter required :) — Kraang Prime, Dec 25 '16 at 19:32
I am talking about storing multiple email ids in a single cell. — Gurwinder Singh, Dec 25 '16 at 19:33
@GurwinderSingh - This is actually a bit faster (in our specific use), and it requires less memory on the server than breaking out into another associated table. It isn't used frequently enough to benefit from segregating into a new table. That being said, we could go crazy and just make 1 base table of id's and have name, address, phone, etc.. all separate tables (each) in each part relative to each other -- on the extreme example. Most people just consolidate to a single record in a main "contacts" table for primary data. — Kraang Prime, Dec 25 '16 at 19:37
@GurwinderSingh - the information on that page is written to hard-code a specific numeric maximum (from the initial solution), and thus only relates to that specific example. I offered the two fields as an example of getting multiple fields related. Not just a fixed integer rebuild -.- (1-5) as that example provides. Looking at the unaccepted solutions now which seem more viable, but still rather convoluted since I have already written SPLIT() functionality :S — Kraang Prime, Dec 25 '16 at 19:44

score 1 · Accepted Answer · answered Dec 25 '16 at 20:09

you can do it with a query like this

SELECT * FROM (
    SELECT `authorid`
        , TRIM(SUBSTRING_INDEX(emailaddress, ';', 1)) AS email
    FROM authors
UNION ALL
    SELECT `authorid`
        , TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(emailaddress,';;'), ';', 2),';',-1)) AS email
    FROM authors
UNION ALL
    SELECT `authorid`
        , TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(emailaddress,';;'), ';', 3),';',-1)) AS email
    FROM authors
) as emails
WHERE email is not null AND email <> '';

Sample

mysql> SELECT * FROM authors;                                                                                                                                                                            +----+----------+-------------------------------------------+
| id | authorid | emailaddress                              |
+----+----------+-------------------------------------------+
|  1 |      123 | john@smith.com;sue@test.org               |
|  2 |     5271 | sally@john.doe                            |
|  3 |      834 | jacob@tom.smith;foo@bar.net; big@data.cow |
|  4 |       27 | tuesday@rubys.eat                         |
|  5 |     1977 | NULL                                      |
|  6 |      224 |                                           |
|  7 |       88 | miles@per.hour                            |
+----+----------+-------------------------------------------+
7 rows in set (0,00 sec)

mysql> SELECT * FROM (
    ->     SELECT `authorid`
    ->         , TRIM(SUBSTRING_INDEX(emailaddress, ';', 1)) AS email
    ->     FROM authors
    -> UNION ALL
    ->     SELECT `authorid`
    ->         , TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(emailaddress,';;'), ';', 2),';',-1)) AS email
    ->     FROM authors
    -> UNION ALL
    ->     SELECT `authorid`
    ->         , TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(emailaddress,';;'), ';', 3),';',-1)) AS email
    ->     FROM authors
    -> ) as emails
    -> WHERE email is not null AND email <> '';
+----------+-------------------+
| authorid | email             |
+----------+-------------------+
|      123 | john@smith.com    |
|     5271 | sally@john.doe    |
|      834 | jacob@tom.smith   |
|       27 | tuesday@rubys.eat |
|       88 | miles@per.hour    |
|      123 | sue@test.org      |
|      834 | foo@bar.net       |
|      834 | big@data.cow      |
+----------+-------------------+
8 rows in set (0,00 sec)

mysql>

Split if field contains delimiter and preserve related row values MySQL query

1 Answers1