Awais Amir
Awais Amir

Reputation: 165

Separate comma separated list into query columns

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?

Upvotes: 1

Views: 2549

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

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

Demo.

The expressions have two common parts:

  • LENGTH(str)-LENGTH(REPLACE(str,',','')) >= K - this subexpression determines if the string has at least K delimiters
  • substring_index(substring_index(str,',',K),',',-1) - this subexpression cuts out the element after the K-th delimiter

Upvotes: 4

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 1

Related Questions