How to split column using mysql?

Question

I want to split my table column data as below.

original table

ID parentID title meta
24    -       E
1    24       A   
2    25       B
3    1        C   1,2,4
4    2        D   1,2,3

Now I want to split

ID meta title
3   1     A
3   2     B
3   4     D

This is what I tried so far

  SELECT tbl_sub.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tbl_sub.metakey, ',', numbers.n), ',', -1) meta 
  FROM
  (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers 
  INNER JOIN tbl_sub
  ON CHAR_LENGTH(tbl_sub.metakey)-CHAR_LENGTH(REPLACE(tbl_sub.meta , ',', ''))>=numbers.n-1
  where id=3
  ORDER BY id, n

Do you have a table somewhere of every possible `meta` value? — eggyal, May 25 '15 at 09:09
@eggyal : im putting meta values as id's. Ex: meta value 1,2 is id of the same table. meta value 1 is id 1 which the matching title is A likewise — jzon, May 25 '15 at 09:10

score 0 · Accepted Answer · answered May 25 '15 at 09:29

0

If your table is called metas, this query will do what you want.

select m2.id, m1.id, m1.title
  from metas m1
    inner join metas m2
      on find_in_set(m1.id, m2.meta);

demo here

Normally find_in_set() is a bit of a crutch around poorly designed tables (which is certainly what this is), but at least you're using it for overall good!

answered May 25 '15 at 09:29

pala_

8,901
1
15
32

will it be possible to put another find_in_set ? – jzon May 26 '15 at 06:39
i have edited your fiddle. what i want is another coloum to get title of id s. Ex: id 24 is parent id as well as id , so i want to get the title with matching for id 24 as well . please advice – jzon May 26 '15 at 06:49

How to split column using mysql?

1 Answers1