I have a table which has the column containing the comma separated list like
ID : List
1 : 1,2,44,5 --row# 1
2 : 4,3,5,2,56,66 --row# 2
and so on. I want to write a select query which would have at max 10 columns Item1, Item2, Item3 .... Item10 and each column has a number from the corresponding comma separated list.
For example: for ID = 1
Item1 = 1, Item2 = 2, Item3 = 44, Item4 = 55 and all other columns would be null or empty
How can I write this in SQL?
You can do it like this:
select
substring_index(substring_index(str,',',1),',',-1)AS c1
, CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 1 THEN substring_index(substring_index(str,',',2),',',-1) ELSE NULL END AS c2
, CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 2 THEN substring_index(substring_index(str,',',3),',',-1) ELSE NULL END AS c3
, CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 3 THEN substring_index(substring_index(str,',',4),',',-1) ELSE NULL END AS c4
, CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 4 THEN substring_index(substring_index(str,',',5),',',-1) ELSE NULL END AS c5
, CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 5 THEN substring_index(substring_index(str,',',6),',',-1) ELSE NULL END AS c6
, CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 6 THEN substring_index(substring_index(str,',',7),',',-1) ELSE NULL END AS c7
, CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 7 THEN substring_index(substring_index(str,',',8),',',-1) ELSE NULL END AS c8
, CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 8 THEN substring_index(substring_index(str,',',9),',',-1) ELSE NULL END AS c9
, CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 9 THEN substring_index(substring_index(str,',',10),',',-1) ELSE NULL END AS c10
from test
The expressions have two common parts:
LENGTH(str)-LENGTH(REPLACE(str,',','')) >= K - this subexpression determines if the string has at least K delimiterssubstring_index(substring_index(str,',',K),',',-1) - this subexpression cuts out the element after the K-th delimiter
This can be done by creating a user-defined function reference from MySQL Split String Function ,pass your column which contains the comma separated string as first parameter to this function,in second parameter pass the separator in your case comma is separator and in third parameter pass your desired position to get the string
SPLIT_STR(string, separator, position)
CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
And then you can query your data a below
SELECT
SPLIT_STR(List, ',', 1) item1,
SPLIT_STR(List, ',', 2) item2,
SPLIT_STR(List, ',', 3) item3,
SPLIT_STR(List, ',', 4) item4,
SPLIT_STR(List, ',', 5) item5,
SPLIT_STR(List, ',', 6) item6,
SPLIT_STR(List, ',', 7) item7,
SPLIT_STR(List, ',', 8) item8,
SPLIT_STR(List, ',', 9) item9,
SPLIT_STR(List, ',', 10) item10
FROM t
Fiddle Demo