Efficient Query to Split a Delimited Column into Separate Rows in Another Table

Efficient query to split a delimited column into separate rows in another table

Create a split function:

CREATE FUNCTION dbo.SplitStrings(@List NVARCHAR(MAX))
RETURNS TABLE
AS
   RETURN ( SELECT Item FROM
       ( SELECT Item = x.i.value('(./text())[1]', 'nvarchar(max)')
         FROM ( SELECT [XML] = CONVERT(XML, '<i>'
         + REPLACE(@List, '.', '</i><i>') + '</i>').query('.')
           ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
       WHERE Item IS NOT NULL
   );
GO

Then get rid of all the cursor and looping nonsense and do this:

INSERT dbo.mrhierlookup
(
  heiraui,
  aui
)
SELECT s.Item, m.aui
  FROM dbo.mrhier3 AS m
  CROSS APPLY dbo.SplitStrings(m.ptr) AS s
GROUP BY s.Item, m.aui;

Efficient query to split a delimited column into separate rows in another table

Create a split function:

CREATE FUNCTION dbo.SplitStrings(@List NVARCHAR(MAX))
RETURNS TABLE
AS
   RETURN ( SELECT Item FROM
       ( SELECT Item = x.i.value('(./text())[1]', 'nvarchar(max)')
         FROM ( SELECT [XML] = CONVERT(XML, '<i>'
         + REPLACE(@List, '.', '</i><i>') + '</i>').query('.')
           ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
       WHERE Item IS NOT NULL
   );
GO

Then get rid of all the cursor and looping nonsense and do this:

INSERT dbo.mrhierlookup
(
  heiraui,
  aui
)
SELECT s.Item, m.aui
  FROM dbo.mrhier3 AS m
  CROSS APPLY dbo.SplitStrings(m.ptr) AS s
GROUP BY s.Item, m.aui;

SQL split values to multiple rows

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

an example fiddle is here.

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 comma separated string table row into separate rows using TSQL

Alternatively, you could use XML like so:

DECLARE @yourTable TABLE(ID INT,SomeValue VARCHAR(25));

INSERT INTO @yourTable
VALUES  (1,'a,b,c,d'),
        (2,'e,f,g');

WITH CTE
AS
(
    SELECT  ID,
            [xml_val] = CAST('<t>' + REPLACE(SomeValue,',','</t><t>') + '</t>' AS XML)
    FROM @yourTable
)

SELECT  ID,
        [SomeValue] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

---

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

---

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

---

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

How to separate values (delimiter separated) as different columns from SQL Server table having multiple rows?

My Input

select * from StringVal

StringVal
57^H:\ ^ 200^Test ^2018-09-19 08:20:01.000
8^T:\ ^ 88^Test1 ^2018-09-1 08:00:01.000
33^D:\ ^ 40^Test2 ^2018-10-1 08:10:01.000

Query

;with cte 
as (
 select 
   CONVERT (varchar (255), StringVal) StringVal
 , convert (varchar (255), StringVal) want -- 'want' means wanted column.
 , ROW_NUMBER () over (partition by StringVal order by (select null)) id  
 -- Row id. Based on original value. for track/count the '^'.
 from StringVal
 union all
 select 
   CONVERT (varchar (255), StringVal) 
 , convert ( varchar (255)
  , stuff (
     want, CHARINDEX ('^', want),1
     ,choose (id, '</FreeSpace ><Drive>','</Drive><Total>', '</Total><Server>','</Server><Date>')
     -- Replace the '^' by XML tags based on column's order.
  )
 ) 
 , id + 1
 from cte
 where want like '%^%'
)
select 
  FreeSpace.value('.', 'varchar (255)') FreeSpace
, Total.value('.', 'varchar (255)') Total
, Drive.value('.', 'varchar (255)') Drive
, Server.value('.', 'varchar (255)') Server
, Date.value('.', 'varchar (255)') Date
from (
 select convert(xml, '<StringVal><FreeSpace>' + want + '</Date></StringVal>') StringVal 
 from cte where id = 5
) xml
cross apply -- I'm not good in XML. so I need lot xml.nodes.
xml.StringVal.nodes('/StringVal/FreeSpace') FreeSpace(FreeSpace)
cross apply 
xml.StringVal.nodes('/StringVal/Total') Total(Total)
cross apply 
xml.StringVal.nodes('/StringVal/Drive') Drive(Drive)
cross apply 
xml.StringVal.nodes('/StringVal/Server') Server(Server)
cross apply 
xml.StringVal.nodes('/StringVal/Date') Date(Date)

Final Output

FreeSpace   Total   Drive   Server  Date
8           88      T:\     Test1   2018-09-1 08:00:01.000
57          200     H:\     Test    2018-09-19 08:20:01.000
33          40      D:\     Test2   2018-10-1 08:10:01.000

---

Related Topics