how to split comma separated string from one column and turn it into several columns?
this is my table:
SELECT id,lik FROM `tbl_users_posts` WHERE id=1;
id lik
-------------
1 10,11,12,13,14,15
how can i split 'lik' column and get this result?
id lik
-------------
1 10
1 11
1 12
1 13
1 14
1 15
displays id 1 in the first row and split the 'lik' column into pieces in the second row and displays it one by one
Unfortunately MySQL doesn't have a split string functions. One way is create a temporary table as following with the max values of the largest row:
create temporary table numbers as (
select 1 as n
union select 2 as n
union select 3 as n
union select 4 as n
union select 5 as n
union select 6 as n
union select 7 as n
union select 8 as n
);
Then you can use substring_index to accomplish the desired result
select id,
substring_index( substring_index(lik, ',', n),',', -1) as lik
from tbl_users_posts
join numbers on char_length(lik) - char_length(replace(lik, ',', '')) >= n - 1
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=84bc1b4e60a7feea5af0d0b568bc7bcb
Edit.
Another method if you have MySQL 8+ for the lik string to split into thousands of pieces without a loop is create an temporary table using recursive cte as follows:
CREATE TEMPORARY TABLE numbers WITH RECURSIVE cte AS
( select 1 as n
union all
select n +1
from cte
limit 1000
)
SELECT * FROM cte;
And then use the same query as above:
select id,
substring_index( substring_index(lik, ',', n),',', -1) as lik
from tbl_users_posts
join numbers on char_length(lik) - char_length(replace(lik, ',', '')) >= n - 1
order by id asc;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9231202418ce9b17aef8609ad6875fbe
If the lik is a number that can be found in the database you can do:
select p.id, t.lik_id
from table_containing_lik t
join tbl_users_posts p on find_in_set(t.lik_id, p.lik)
where p.id=1;