1

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.

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.