mysql字段分隔符拆分_如果字段包含分隔符并且保留MySQL查询的相关行值，则拆分(Split if field contains delimiter and preserve related ro...

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>