Splitting Delimited Values in a SQL Column into Multiple Rows

Splitting delimited values in a SQL column into multiple rows

If you are on SQL Server 2016+

You can use the new STRING_SPLIT function, which I've blogged about here, and Brent Ozar has blogged about here.

SELECT s.[message-id], f.value
  FROM dbo.SourceData AS s
  CROSS APPLY STRING_SPLIT(s.[recipient-address], ';') as f;

If you are still on a version prior to SQL Server 2016

Create a split function. This is just one of many examples out there:

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);
GO

I've discussed a few others here, here, and a better approach than splitting in the first place here.

Now you can extrapolate simply by:

SELECT s.[message-id], f.Item
  FROM dbo.SourceData AS s
  CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;

Also I suggest not putting dashes in column names. It means you always have to put them in [square brackets].

Split column data into multiple rows

Using Jeff's DelimitedSplit8K

; 
with cte as
(
    select  id, prodlines, ItemNumber, Item = ltrim(Item),
        grp = dense_rank() over (partition by id order by replace(replace(ltrim(Item), '(Read)', ''), '(Write)', ''))
    from    #prodLines pl
        cross apply dbo.DelimitedSplit8K(prodlines, ',') c
)
select  id, prodlines, prod = stuff(prod, 1, 1, '')
from    cte c
    cross apply
    (
        select  ',' + Item
        from    cte x
        where   x.id    = c.id
        and x.grp   = c.grp
        order by x.Item
        for xml path('')
    ) i (prod)

How to split comma delimited data from one column into multiple rows

If you are on SQL Server 2016 or better, you can use OPENJSON() to split up the code values instead of cumbersome string operations:

SELECT t.Employee_FullName,
       Code = LTRIM(j.value),
       Hours = MAX(CASE j.[key] 
                   WHEN 0 THEN RegularTime
                   WHEN 1 THEN DoubleTime
                   WHEN 2 THEN Overtime END)
  FROM dbo.MyTable AS t
  CROSS APPLY OPENJSON('["' + REPLACE(t.Code,',','","') + '"]') AS j
  GROUP BY t.Employee_FullName, LTRIM(j.value);

• Example db<>fiddle

Splitting string in column in table which has multiple values into separate rows

split_string return value is a table, so you can't use it as column

SELECT [server], STRING_SPLIT([Environment]), ';')
FROM [[dbo].[serverstable]

Thus: it will give you this error:

What you need is: cross join the t1.environment values

 SELECT [server], t2.value
 FROM [dbo].[serverstable] t1
 CROSS APPLY STRING_SPLIT(t1.[Environment], ';') t2

Split multiple values from a string in one column, into multiple columns using SQL Server

With a bit of JSON and assuming you have a known or maximum number of tags

Select A.CompanyName
      ,A.CompanyNumber
      ,Tag1  = JSON_VALUE(S,'$[0]')
      ,Tag2  = JSON_VALUE(S,'$[1]')
      ,Tag3  = JSON_VALUE(S,'$[2]')
From  YourTable A
Cross Apply ( values ( '["'+replace(STRING_ESCAPE(Tags,'json'),';','","')+'"]' ) ) B(S)

---

Related Topics