MYSQL: Split string with variable number of occurances

Question

I have a column in a table which contains person's details in this format:

+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  Team  |                                                                                                Members                                                                                                 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Team 1 | OK-10:Jason:Jones:ID No:00000000:male:my notes                                                                                                                                                         |
| Team 2 | OK-10:Mike:James:ID No:00000001:male:my notes OZ-09:John:Rick:ID No:00000002:male:my notes                                                                                                             |
| Team 3 | OK-08:Michael:Knight:ID No:00000004:male:my notes2 OK-09:Helen:Rick:ID No:00000005:female:my notes3 OZ-10:Jane:James:ID No:00000034:female:my notes23 OK-09:Mary:Jane:ID No:00000023:female:my notes46 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I want to split them in a new table like this:

+-------+-------------+-------------+----------------+------------------+---------------+---------------+--------------+
| Team  | Member_Rank | Member_Name | Member_Surname | Member_ID_Method | Member_ID_Num | Member_Gender | Member_Notes |
+-------+-------------+-------------+----------------+------------------+---------------+---------------+--------------+
| Team1 | OK-10       | Jason       | Jones          | ID No            |      00000000 | male          | my notes     |
| Team2 | OK-10       | Mike        | James          | ID No            |      00000001 | male          | my notes     |
| Team2 | OZ-09       | John        | Rick           | ID No            |      00000002 | male          | my notes     |
+-------+-------------+-------------+----------------+------------------+---------------+---------------+--------------+

Splitting details:

Split Row Delimiter : ' O&-' where & can be only 'K' or 'Z'

Split Column Delimiter : ':'

One Team can contain many members, there is no upper limit

Is that possible?

You should read the existing data and handle the transformation in your program logic and not in SQL. — juergen d
– juergen d, Commented Sep 3, 2019 at 10:23
@juergend data is imported from CSV which I can't change its export procedure — netdev
– netdev, Commented Sep 3, 2019 at 10:31
If it is CSV from the start then read that into your program. Change it the way you like and afterwards save it in SQL — juergen d
– juergen d, Commented Sep 3, 2019 at 10:40
@juergend there is no program at all. I just have to import it to an existing database. Its a database migration project. — netdev
– netdev, Commented Sep 3, 2019 at 10:41
Then I would write a simple program to do that. There are plenty CSV readers out there and it is way easier to handle in java or C# than in SQL — juergen d
– juergen d, Commented Sep 3, 2019 at 10:47

Tim Biegeleisen · Accepted Answer · 2019-09-03 10:25:47Z

1

We can handle this using SUBSTRING_INDEX, but it is fairly ugly:

SELECT
    Team,
    SUBSTRING_INDEX(text, ':', 1) AS Member_Rank,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(text, ':', 2), ':', -1) AS Member_Name,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(text, ':', 3), ':', -1) AS Member_Surname,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(text, ':', 4), ':', -1) AS Member_ID_Method,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(text, ':', 5), ':', -1) AS Member_ID_Num,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(text, ':', 6), ':', -1) AS Member_Gender,
    SUBSTRING_INDEX(text, ':', -1) AS Member_Notes
FROM yourTable;

But I personally might reformat/split your data outside of MySQL, e.g. in an application language such as Java, and then reimport it as separate columns.

answered Sep 3, 2019 at 10:25

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

netdev Over a year ago

Thank you, but while it splits the strings, it doesn't create a new row if there are more than one member in a cell (using the ' O&:' delimiter)

Tim Biegeleisen Over a year ago

I suggest not handling this within MySQL then. Handle it in your application language.

netdev Over a year ago

well csv is not exported from an application I have access to... So I don't think that I can manipulate it before importing it

Tim Biegeleisen Over a year ago

Use the right tool for the right job. MySQL is not a text parsing tool (Perl is though).

netdev Over a year ago

I am using a batch file in order to import it in phpmyadmin, I don't know if its possible to edit the csv in order to split the rows using batch script....

|

Collectives™ on Stack Overflow

MYSQL: Split string with variable number of occurances

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related