Sql-server – SQL Server (Azure) split comma-separated string into multiple columns

azure-sql-databasesql-server-2016

I have the following data in Azure SQL Server (Microsoft SQL Azure (RTM) – 12.0.2000.8 Sep 20 2018 21:40:02 Copyright (C) 2018 Microsoft Corporation)

ID    Name
1     US_Android_100x200_blue
2     UK_iOS_100x200_green

I want to split them into a table like this:

ID  Country  Device   Size     Color
1   US       Android  100x200  blue
2   UK       iOS      100x200  green

What are the ways to do it? I looked it up and it shows to use STRING_SPLIT but I haven't been able to find a clean way to do it.

Best Answer

STRING_SPLIT splits a string into a set consisting of a single column, not into multiple columns in the same row.

If there are always 4 components, you can use this long thing:

DECLARE @x TABLE(ID int,    Name sysname)
INSERT @x VALUES
(1,'     US_Android_100x200_blue'),
(2,'     UK_iOS_100x200_green');

;WITH x AS
(
  SELECT x.ID, y.value, rn = ROW_NUMBER() OVER 
    (PARTITION BY x.ID ORDER BY CHARINDEX('_' + value + '_', '_' + x.name + '_')) 
  FROM @x AS x CROSS APPLY STRING_SPLIT(x.name, '_') AS y
)
SELECT ID, Country = [1], Device = [2], [Size] = [3], Color = [4]
FROM x PIVOT (MAX(value) FOR rn IN ([1],[2],[3],[4])) AS p
ORDER BY ID;

Or you can hack it:

;WITH x AS 
(
  SELECT ID, Name = REPLACE(Name, '_', '.') FROM @x
)
SELECT ID, 
  Country = PARSENAME(Name, 4),
  Device  = PARSENAME(Name, 3),
  Size    = PARSENAME(Name, 2),
  Color   = PARSENAME(Name, 1)
FROM x
ORDER BY ID;
Related Question