How to retrieve multiple data that store inside the same database column?

Question

My database is like

Name  |  IC   |   Item
--------------------------
lee   |  xxx  |   pear,bear
--------------------------
ron   |  xxx   |  apple,dog

what should I do to retrieve the 4 values contained in the column "Item" and then separate them?

What output do you actually want? Have you tried something yet? — Tim Biegeleisen, Mar 10 '17 at 08:04
Possible duplicate of [How to split the name string in mysql?](http://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql) — Andrey Korneyev, Mar 10 '17 at 08:04
Never, ever store data as comma separated items. It will only cause you lots of trouble. — jarlh, Mar 10 '17 at 08:05
hmm..its part of the project database. I cant edit or change the design half way now. So is there any solution? — ron, Mar 10 '17 at 08:19

score 0 · Answer 1 · edited May 23 '17 at 11:54

Do you have only two items separated by comma in Item? Or it may vary?

LE: you can use this SQL split comma separated row LLE: just played around with that and this what I've done:

create table myTable(name varchar(7), ic varchar(7), item varchar(200));

insert into myTable(name,ic,item) values ('lee','xxx','pear,bear');
insert into myTable(name,ic,item) values ('ron','xxx','apple,dog');
insert into myTable(name,ic,item) values ('a','xxx','gamma');
insert into myTable(name,ic,item) values ('b','xxx','a,b,c,d');

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.item, ',', n.n), ',', -1) value
FROM myTable t CROSS JOIN 
(
    SELECT a.N + b.N * 10 + 1 n
    FROM 
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
        ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(t.item) - LENGTH(REPLACE(t.item, ',', '')))
ORDER BY value;

How to retrieve multiple data that store inside the same database column?

1 Answers1