-1

Just aiming for a select query which splits a column into 5 columns separated by their comma's. The column (data_key) consistently has four comma's like this (CL,J51816940,1,789770,1), but not at the same n'th position in the field. The first, second and final column are okay, but creating column 3 and 4 is proving difficult. Any help would be much appreciated.

    select top 1000
left(data_key, charindex(',', data_key)-1) start,
SUBSTRING(data_key, CHARINDEX(',', data_key)+1, CHARINDEX(',', data_key, CHARINDEX(',', data_key)+1) -  CHARINDEX(',', data_key) -1 )  two, 
SUBSTRING(data_key, CHARINDEX(',', data_key, 2)+1, (charindex(',', data_key, 1))) three,
SUBSTRING(data_key, CHARINDEX(',', data_key)+1, LEN(data_key)-CHARINDEX(',', data_key)-CHARINDEX(',',REVERSE(data_key ))) four,
 RIGHT(data_key, CHARINDEX(',', REVERSE(data_key))-1) five, data_key
FROM table
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Tom Longworth
  • 21
  • 1
  • 1
    Does this answer your question? [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) – Thom A Sep 27 '21 at 10:35
  • 3
    Storing data as comma separated values is usually a bad idea. – jarlh Sep 27 '21 at 10:39
  • `CHAR_INDEX` accepts the starting position for search. So find first comma, use that position + 1 to find second comma and so on. – Salman A Sep 27 '21 at 10:56
  • `SELECT JSON_VALUE(YourColAsJsonArray, '$[0]'), JSON_VALUE(YourColAsJsonArray, '$[1]'), JSON_VALUE(YourColAsJsonArray, '$[2]'), JSON_VALUE(YourColAsJsonArray, '$[3]'), JSON_VALUE(YourColAsJsonArray, '$[4]') FROM YourTable CROSS APPLY (VALUES (CONCAT('["', REPLACE(YourCol, ',', '","'), '"]'))) V(YourColAsJsonArray)` – Martin Smith Sep 27 '21 at 11:05
  • What is your SQL Server version? – Yitzhak Khabinsky Sep 27 '21 at 13:47

1 Answers1

1

CHARINDEX takes a starting parameter. You can use CROSS APPLY (VALUES to feed in one value into the next

select top 1000
    left(data_key, v1.comma - 1) start,
    SUBSTRING(data_key, v1.comma + 1, v2.comma - v1.comma - 1) two, 
    SUBSTRING(data_key, v2.comma + 1, v3.comma - v2.comma - 1) three,
    SUBSTRING(data_key, v3.comma + 1, v4.comma - v3.comma - 1) four,
    SUBSTRING(data_key, v4.comma + 1, LEN(data_key)) five,
    data_key
FROM [table]
CROSS APPLY (VALUES( NULLIF(CHARINDEX(',', data_key), 0) )) v1(comma)
CROSS APPLY (VALUES( NULLIF(CHARINDEX(',', data_key, v1.comma + 1), 0) )) v2(comma)
CROSS APPLY (VALUES( NULLIF(CHARINDEX(',', data_key, v2.comma + 1), 0) )) v3(comma)
CROSS APPLY (VALUES( NULLIF(CHARINDEX(',', data_key, v3.comma + 1), 0) )) v4(comma);

db<>fiddle

Charlieface
  • 52,284
  • 6
  • 19
  • 43