CODEWITHSUNDEEP
mysqlsql
Craig Tucker
Craig Tucker

Reputation: 1071

SQL parsing delimited data in one column out to two other columns

I have an existing table in MySQL with data synchronized data that ends up looking something like this:

id      client      notes           id1         id2
------- ---------   ------------    ----------  ----------
1       a           cat dog|3|90
2       b
3       c           |80|12
4       d           dog only
5       e
6       f           |34|40

I want it to look like this:

id      client      notes           id1         id2
------- ---------   ------------    ----------  ----------
1       a           cat dog|3|90    3           90
2       b
3       c           |80|12          80          12  
4       d           dog only
5       e
6       f           |34|40          34          40

What is the SQL logic to accomplish this? The delimiter will always be two pipes. That is I want a statement that I can apply to the table in phpMyAdmin to clean up the sync and separate out the id's properly.

Upvotes: 2

Views: 94

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use substring_index():

select id, client, notes,
       substring_index(substring_index(notes, '|', 2), '|', -1) as id1,
       substring_index(notes, '|', -1) as id2
from . . .;

Upvotes: 1

Related Questions