DROP FUNCTION dbo.DelimitedSplit8K
GO
 CREATE FUNCTION dbo.Split8K
/***************************************************************************************************
 Purpose:
 Split a given string at a given delimiter and return a list of the split elements (items).

 Returns:
 iTVF containing the following:
 ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)
 Item       = Element value as a VARCHAR(8000)

 CROSS APPLY Usage Example:
---------------------------------------------------------------------------------------------------
--===== Conditionally drop the test tables to make reruns easier for testing.
     -- (this is NOT a part of the solution)
     IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL 
        DROP TABLE #JBMTest
;
--===== Create and populate a test table on the fly (this is NOT a part of the solution).
 SELECT *
   INTO #JBMTest
   FROM (
         SELECT 1,'1,10,100,1000,10000,100000,1000000' UNION ALL
         SELECT 2,'2000000,200000,20000,2000,200,20,2' UNION ALL
         SELECT 3, 'This,is,a,test'                    UNION ALL
         SELECT 4, 'and so is this'                    UNION ALL
         SELECT 5, 'This, too (no pun intended)'
        ) d (SomeID,SomeValue)
;
GO
--===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)
 SELECT test.SomeID, split.ItemNumber, split.Item
   FROM #JBMTest test
  CROSS APPLY
        (
         SELECT ItemNumber, Item
           FROM dbo.DelimitedSplit8k(test.SomeValue,',')
        ) split
;
---------------------------------------------------------------------------------------------------
 Notes:
 1. Optimized for VARCHAR(7999) or less.  No testing or error reporting for truncation at 7999
    characters is done.
 2. Optimized for single character delimiter.  Multi-character delimiters should be resolved
    externally from this function.
 3. Optimized for use with CROSS APPLY.
 4. Does not "trim" elements just in case leading or trailing blanks are intended.
 5. If you don't know how a Tally table can be used to replace loops, please see the following...
    http://www.sqlservercentral.com/articles/T-SQL/62867/
 6. Changing this function to use VARCHAR(MAX) will cause it to run twice as slow.  It's just the 
    nature of VARCHAR(MAX) whether it fits in-row or not.  Some recovery of speed can be realized
    by doing datatype matching for CHARINDEX which changes it's datatype for VARCHAR(MAX).
 7. Multi-machine testing for the method of using UNPIVOT instead of 10 SELECT/UNION ALLs shows
    that the UNPIVOT method is quite machine dependent and can slow things down quite a bit.
 8. Performance testing shows using "TOP" for the limiting criteria of "N" is actually 
    slower and slightly more CPU intensive than the traditional WHERE N < LEN(@pString) + 2. 
 9. Performance testing shows using ORDER BY (SELECT x) where "x" is anything is actually
    slower and slightly more CPU intensive than the traditional ORDER BY (SELECT N).
***************************************************************************************************/
--===== Define I/O parameters
        (
        @pString    VARCHAR(7999),
        @pDelimiter CHAR(1)
        )
RETURNS TABLE
   WITH SCHEMABINDING
AS
RETURN
--===== "Inline" CTE Driven "Tally Table” produces values up to
     -- 10,000... enough to cover VARCHAR(8000)
WITH
      E1(N) AS ( --=== Create Ten 1's
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 --10
               ),
      E2(N) AS (SELECT 1 FROM E1 a, E1 b),   --100
      E4(N) AS (SELECT 1 FROM E2 a, E2 b),   --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4)  
--===== Do the split
 SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
        SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
   FROM cteTally
  WHERE N <= DATALENGTH(@pString)+1 --DATATLENGTH allows for trailing space delimiters
    AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
GO

DROP FUNCTION dbo.DelimitedSplit8K
GO
 CREATE FUNCTION dbo.Split8K
/***************************************************************************************************
 Purpose:
 Split a given string at a given delimiter and return a list of the split elements (items).

 Returns:
 iTVF containing the following:
 ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)
 Item       = Element value as a VARCHAR(8000)

 CROSS APPLY Usage Example:
---------------------------------------------------------------------------------------------------
--===== Conditionally drop the test tables to make reruns easier for testing.
     -- (this is NOT a part of the solution)
     IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL 
        DROP TABLE #JBMTest
;
--===== Create and populate a test table on the fly (this is NOT a part of the solution).
 SELECT *
   INTO #JBMTest
   FROM (
         SELECT 1,'1,10,100,1000,10000,100000,1000000' UNION ALL
         SELECT 2,'2000000,200000,20000,2000,200,20,2' UNION ALL
         SELECT 3, 'This,is,a,test'                    UNION ALL
         SELECT 4, 'and so is this'                    UNION ALL
         SELECT 5, 'This, too (no pun intended)'
        ) d (SomeID,SomeValue)
;
GO
--===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)
 SELECT test.SomeID, split.ItemNumber, split.Item
   FROM #JBMTest test
  CROSS APPLY
        (
         SELECT ItemNumber, Item
           FROM dbo.DelimitedSplit8k(test.SomeValue,',')
        ) split
;
---------------------------------------------------------------------------------------------------
 Notes:
 1. Optimized for VARCHAR(7999) or less.  No testing or error reporting for truncation at 7999
    characters is done.
 2. Optimized for single character delimiter.  Multi-character delimiters should be resolved
    externally from this function.
 3. Optimized for use with CROSS APPLY.
 4. Does not "trim" elements just in case leading or trailing blanks are intended.
 5. If you don't know how a Tally table can be used to replace loops, please see the following...
    http://www.sqlservercentral.com/articles/T-SQL/62867/
 6. Changing this function to use VARCHAR(MAX) will cause it to run twice as slow.  It's just the 
    nature of VARCHAR(MAX) whether it fits in-row or not.  Some recovery of speed can be realized
    by doing datatype matching for CHARINDEX which changes it's datatype for VARCHAR(MAX).
 7. Multi-machine testing for the method of using UNPIVOT instead of 10 SELECT/UNION ALLs shows
    that the UNPIVOT method is quite machine dependent and can slow things down quite a bit.
 8. Performance testing shows using "TOP" for the limiting criteria of "N" is actually 
    slower and slightly more CPU intensive than the traditional WHERE N < LEN(@pString) + 2. 
 9. Performance testing shows using ORDER BY (SELECT x) where "x" is anything is actually
    slower and slightly more CPU intensive than the traditional ORDER BY (SELECT N).
***************************************************************************************************/
--===== Define I/O parameters
        (
        @pString    VARCHAR(7999),
        @pDelimiter CHAR(1)
        )
RETURNS TABLE
   WITH SCHEMABINDING
AS
RETURN
--===== "Inline" CTE Driven "Tally Table” produces values up to
     -- 10,000... enough to cover VARCHAR(8000)
WITH
      E1(N) AS ( --=== Create Ten 1's
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 --10
               ),
      E2(N) AS (SELECT 1 FROM E1 a, E1 b),   --100
      E4(N) AS (SELECT 1 FROM E2 a, E2 b),   --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4)  
--===== Do the split
 SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
        SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
   FROM cteTally
  WHERE N <= DATALENGTH(@pString)+1 --DATATLENGTH allows for trailing space delimiters
    AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
GO

quote:

---

Originally posted by madhivanan

Another method would be to use XML




Declare @Text Varchar(100),@delimiter NVARCHAR(5)
Set @Text = 'can you split this?'
set @delimiter = ' '
Declare @textXml Xml
Select @TextXml = Cast('<d>' + Replace(@Text, @Delimiter,'</d><d>') + '</d>' As Xml );
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)


Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by Jeff Moden

@Madhivanan,

Hey there, ol' friend. There are (IIRC) three different XML splitters out there and two of them are pretty bad for performance. Your's is a bit different than any of the other 3 I've seen. If you'd convert it to a function (I hate to change other people's code and then test it... I don't want to muck it up), I'd be happy to run this through the CSV Gin Mill.


--Jeff Moden

---

create function dbo.split_xml
(
	@data varchar(8000),
	@delimiter char(1)
)
returns @split table(data varchar(8000))
as
begin
Declare @textXml Xml
Select @TextXml = Cast('<d>' + Replace(@data, @delimiter,'</d><d>') + '</d>' As Xml )
insert into @split(data)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
return
end

GO

--Usage
select * from dbo.split_xml('can you split this?',' ')

--=================================================================================================
--      Create a CSV test table with 10000 rows and 10 random CSV elements per row.
--      The randomness of the elements also prevents the delimiters for showing up in the same 
--      position for each row.  SQL Server would figure that out and cache the information making
--      some splitting techniques seem faster than they really are.
--      This section of the code takes just a couple of seconds to run because XML concatenation
--      is very fast (especially when compared to XML splitting or shredding).
-- Jeff Moden
--=================================================================================================
--===== Declare and preset a couple of control variables
DECLARE @NumberOfRows           INT,
        @NumberOfElementsPerRow INT
;
 SELECT @NumberOfRows           = 10000,
        @NumberOfElementsPerRow = 10
;
--===== Conditionally drop the test table to make reruns easier
     IF OBJECT_ID('TempDB..#CsvTest','U') IS NOT NULL
        DROP TABLE #CsvTest
;
--===== This creates and populates a test table on the fly containing a
     -- sequential column and a randomly generated CSV Parameter column.
 SELECT TOP (@NumberOfRows) --Controls the number of rows in the test table
        ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)),0) AS RowNum,
        (
         SELECT CAST(STUFF( --=== STUFF get's rid of the leading comma
                ( --=== This builds CSV row with a leading comma
                 SELECT TOP (@NumberOfElementsPerRow) --Controls the number of CSV elements in each row
                        ','+CAST(ABS(CHECKSUM(NEWID()))%100000 AS VARCHAR(10))
                   FROM Master.sys.All_Columns ac3      --Classic cross join pseudo-cursor
                  CROSS JOIN Master.sys.All_Columns ac4 --can produce row sets up 16 million.
                  WHERE ac3.Object_ID <> ac1.Object_ID   --Without this line, all rows would be the same.
                    FOR XML PATH('')
                )
                ,1,1,'') AS NVARCHAR(MAX))
        ) AS CsvParameter
   INTO #CsvTest
   FROM Master.sys.All_Columns ac1      --Classic cross join pseudo-cursor
  CROSS JOIN Master.sys.All_Columns ac2 --can produce row sets up 16 million
;
--===== Let's add a PK just for grins.  Since it's a temp table, we won't name it.
  ALTER TABLE #CsvTest
    ADD PRIMARY KEY CLUSTERED (RowNum) WITH FILLFACTOR = 100
;
--===== Sanity check... let's see what we've got in the test table
 SELECT * FROM #CsvTest
;

   DBCC FREEPROCCACHE
  PRINT '========== Tally-on-the-fly Splitter ==========';
    SET STATISTICS TIME ON;
 SELECT src.RowNum, split.* 
   FROM #CsvTest src
  CROSS APPLY dbo.Split8K(src.CsvParameter,',') split;
    SET STATISTICS TIME OFF;

   DBCC FREEPROCCACHE
  PRINT '========== XML Splitter ==========';
    SET STATISTICS TIME ON;
 SELECT src.RowNum, split.*
   FROM #CsvTest src
  CROSS APPLY dbo.split_xml(src.CsvParameter,',') split;
    SET STATISTICS TIME OFF;

   DBCC FREEPROCCACHE
  PRINT '========== While Loop Splitter ==========';
    SET STATISTICS TIME ON;
 SELECT src.RowNum, split.*
   FROM #CsvTest src
  CROSS APPLY dbo.split(src.CsvParameter,',') split;
    SET STATISTICS TIME OFF;

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== Tally-on-the-fly Splitter ==========

(100000 row(s) affected)

SQL Server Execution Times:
   CPU time = 2469 ms,  elapsed time = 6273 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== XML Splitter ==========

(100000 row(s) affected)

SQL Server Execution Times:
   CPU time = 25093 ms,  elapsed time = 31378 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== While Loop Splitter ==========

(100000 row(s) affected)

SQL Server Execution Times:
   CPU time = 43875 ms,  elapsed time = 66742 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== Tally-on-the-fly Splitter ==========

(1000000 row(s) affected)

SQL Server Execution Times:
   CPU time = 60281 ms,  elapsed time = 98922 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
========== XML Splitter ==========

(1000000 row(s) affected)

SQL Server Execution Times:
   CPU time = 104922 ms,  elapsed time = 155466 ms.

quote:

---

Originally posted by madhivanan

Jeff, can you highlight the change you have made?

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by Jeff Moden

quote:

---

Originally posted by madhivanan

Jeff, can you highlight the change you have made?

Madhivanan

Failing to plan is Planning to fail

---

First, thanks for the feedback, Madhivanan. It's always a pleasure working with you.

I'm not sure what you mean, though. What change do you speak of?


--Jeff Moden

---

quote:

---

Originally posted by Jeff Moden

Ah... that. There was nothing in the code or the test results that I changed. I had made a comment and thought afterwards about WHILE Loops and decided that some folks might have taken it the wrong way so I changed it to "We should all probably steer clear of the While Loop for splitting."


--Jeff Moden

---