SQL Server CLR and SQL split functions to parse a delimited string

By:   |   Updated: 2013-01-14   |   Comments (23)   |   Related: > Functions User Defined UDF


Problem

There are several tips and articles on the internet that discuss how to split a delimited list into multiple rows. This tip shows two approaches to this problem a T-SQL function and a CLR function.

Solution

The following examples show two different approaches to splitting a delimited list into multiple rows. Both approaches return the same result set, but this tip will show the different techniques to deploy the functions.

T-SQL Version

There are several versions of a T-SQL approach that you can find on the internet, but here is one that parses a string based on a delimiter and returns one row per value. The code has been put together to show you how this is done versus finding the most optimal approach for this problem.

Just copy the code below and execute it in a query window.

CREATE FUNCTION [dbo].[fnParseStringTSQL] (@string NVARCHAR(MAX),@separator NCHAR(1))
RETURNS @parsedString TABLE (string NVARCHAR(MAX))
AS 
BEGIN
   DECLARE @position int
   SET @position = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
         INSERT into @parsedString
         SELECT substring(@string, @position, charindex(@separator,@string,@position) - @position)
         SET @position = charindex(@separator,@string,@position) + 1
      END
     RETURN
END

CLR Version

This can be done either manually or you can do this using Visual Studio. If you deploy from Visual Studio a lot of these steps are simplified.

Step 1 - CLR code

Before we get started the first thing that needs to be done is to enable the CLR on your SQL Server. This can be done by using the SQL Server Surface Area Configuration tool. Refer to this tip CLR String Sort Function in SQL Server 2005 for more information.

Copy and save the VB.Net code below in a file called: C:\fnParseString.vb or whatever you prefer.

Imports System.Collections
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction( _
                FillRowMethodName:="GetNextToken", _
                 TableDefinition:="StringCol NVARCHAR(MAX)")> _
    Public Shared Function parseStringCLR( ByVal Input As String, ByVal Separator As Char) As IEnumerable
        Dim Result() As String
        Result = Input.Split(Separator)
        Return Result
    End Function   
    Public Shared Sub GetNextToken(ByVal row As Object, ByRef TheToken As String)
        TheToken = CStr(row)
    End Sub
End Class

Step 2 - Compile CLR Code - (You only need to do this if you are not using Visual Studio to develop and deploy)

In order to use this code, the code has to be compiled first to create a DLL.

The following command is run from a command line to compile the CLR code using the vbc.exe application. This is found in the .NET 2.0 framework directory. This may be different on your server or desktop. Also, this code should be compiled on the machine where the code will run.

So from a command line run the following command:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc /target:library C:\fnParseString.vb

The code should now be compiled in a file called: C:\fnParseString.dll

Step 3 - Create Assembly and Function - (You only need to do this if you are not using Visual Studio to develop and deploy)

After the code has been compiled you need to create the assembly and the function with SQL Server. To do this, run these commands in the database where you want to create the function.

For the function you will see three components that are referenced CLRFunctions.UserDefinedFunctions .parseStringCLR .

  • CLRFunctions - the assembly reference
  • UserDefinedFunctions - the class reference in the VB code
  • parseStringCLR - the function reference in the VB code
CREATE ASSEMBLY CLRFunctions FROM 'C:\fnParseString.dll' 
GO

CREATE FUNCTION [dbo].fnParseStringCLR(@string [nvarchar](4000), @separator [nchar](1))
RETURNS TABLE (
[StringCol] [nvarchar](max) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME CLRFunctions.UserDefinedFunctions.parseStringCLR

Step 4 - Test It

To test the functions, run the following SELECT statements.

SELECT * FROM dbo.fnParseStringTSQL('SQL Server 2000|SQL Server 2005|SQL Server 2008|SQL Server 7.0','|')
GO
SELECT * FROM dbo.fnParseStringCLR('SQL Server 2000|SQL Server 2005|SQL Server 2008|SQL Server 7.0','|')
GO
SELECT * FROM dbo.fnParseStringTSQL('Apple,Banana,Pear',',')
GO
SELECT * FROM dbo.fnParseStringCLR('Apple,Banana,Pear',',')
GO

Here is the output from the above queries:

Result set for the first two queries

p1

Result set for the second two queries

p2

Here is another test that has over 1000 delimited items. With this amount of data we can see that the CLR code is faster. Although each time you run this you may get different execution times, in my tests the CLR was always almost three times faster.

SELECT * FROM dbo.fnParseStringTSQL( '980,365,771,404,977,818,474,748,975,884,347,521,396,791,775,848,438,715,492,781,679,912,830,950,788,
387,738,435,799,475,429,415,967,373,721,882,717,708,837,393,740,360,954,990,794,378,327,741,468,516,
832,753,970,420,744,531,761,811,874,504,747,332,809,897,956,368,409,716,968,386,846,441,431,458,418,
961,479,507,505,367,454,523,425,872,804,981,964,328,362,861,927,841,765,423,939,326,766,714,405,344,
72,494,850,955,770,923,926,722,810,867,400,789,871,900,526,513,364,935,512,371,436,384,316,892,875,
988,906,374,895,911,844,798,745,342,319,330,760,983,478,978,783,973,986,868,467,855,853,388,449,773,
801,529,887,948,838,735,839,793,397,518,893,728,440,860,511,994,831,750,833,524,680,883,466,899,907,
375,933,985,942,414,486,905,894,739,462,885,940,357,514,443,749,754,946,947,725,343,473,452,807,931,
790,959,930,898,896,965,483,510,957,322,460,951,996,982,859,448,966,366,713,445,497,491,515,787,915,
743,470,972,932,944,489,485,732,525,758,772,379,806,325,411,976,805,755,395,752,389,958,995,417,902,
4,727,987,936,506,889,925,402,324,731,851,352,464,392,891,345,795,945,803,917,825,482,469,937,840,
434,383,496,488,450,828,377,774,391,706,934,484,522,729,819,842,490,908,422,763,757,826,920,916,350,
922,461,432,463,834,733,530,780,351,815,863,487,876,824,974,370,356,407,821,394,446,724,382,928,742,
465,817,528,778,969,341,372,999,849,711,442,730,428,857,869,993,759,918,406,710,862,949,796,878,919,
843,881,533,971,401,943,444,784,723,509,800,814,426,412,457,476,455,822,477,720,493,910,416,355,764,
410,421,984,782,736,707,776,471,989,456,903,886,317,914,430,802,827,779,880,399,953,459,962,904,519,
726,433,852,769,997,888,439,879,709,756,527,762,734,390,424,941,835,359,2,921,346,864,866,960,1,858,
719,369,532,979,413,358,323,398,534,873,447,321,403,495,348,991,913,823,797,820,816,847,385,865,376,
746,777,520,381,854,829,767,768,481,361,408,718,331,380,877,751,924,792,813,786,963,812,318,363,329,
929,3,535,427,992,909,508,437,938,320,998,419,901,517,349,856,808,480,870,737,785,453,890,952,451,
836,845,712,975,884,347,521,396,791,775,848,438,715,492,781,679,912,830,950,788,387,738,435,799,475,
429,415,967,373,721,882,717,708,837,393,740,360,954,990,794,378,327,741,468,516,832,753,970,420,744,
531,761,811,874,504,747,332,809,897,956,368,409,716,968,386,846,441,431,458,418,961,479,507,505,367,
454,523,425,872,804,981,964,328,362,861,927,841,765,423,939,326,766,714,405,344,472,494,850,955,770,
923,926,722,810,867,400,789,871,900,526,513,364,935,512,371,436,384,316,892,875,988,906,374,895,911,
844,798,745,342,319,330,760,983,478,978,783,973,986,868,467,855,853,388,449,773,801,529,887,948,838,
735,839,793,397,518,893,728,440,860,511,994,831,750,833,524,680,883,466,899,907,375,933,985,942,414,
486,905,894,739,462,885,940,357,514,443,749,754,946,947,725,343,473,452,807,931,790,959,930,898,896,
965,483,510,957,322,460,951,996,982,859,448,966,366,713,445,497,491,515,787,915,743,470,972,932,944,
489,485,732,525,758,772,379,806,325,411,976,805,755,395,752,389,958,995,417,902,4,727,987,936,506,
889,925,402,324,731,851,352,464,392,891,345,795,945,803,917,825,482,469,937,840,434,383,496,488,450,
828,377,774,391,706,934,484,522,729,819,842,490,908,422,763,757,826,920,916,350,922,461,432,463,834,
733,530,780,351,815,863,487,876,824,974,370,356,407,821,394,446,724,382,928,742,465,817,528,778,969,
341,372,999,849,711,442,730,428,857,869,993,759,918,406,710,862,949,796,878,919,843,881,533,971,401,
943,444,784,723,509,800,814,426,412,457,476,455,822,477,720,493,910,416,355,764,410,421,984,782,736,
707,776,471,989,456,903,886,317,914,430,802,827,779,880,399,953,459,962,904,519,726,433,852,769,997,
888,439,879,709,756,527,762,734,390,424,941,835,359,2,921,346,864,866,960,1,858,719,369,532,979,413,
358,323,398,534,873,447,321,403,495,348,991,913,823,797,820,816,847,385,865,376,746,777,520,381,854,
829,767,768,481,361,408,718,331,380,877,751,924,792,813,786,963,812,318,363,329,929,3,535,427,992,
909,508,437,938,320,998,419,901,517,349,856,808,480,870,737,785,453,890,952,451,836,845,712' ,',')

p3

SELECT * FROM dbo.fnParseStringCLR( '980,365,771,404,977,818,474,748,975,884,347,521,396,791,775,848,438,715,492,781,679,912,830,950,788,
387,738,435,799,475,429,415,967,373,721,882,717,708,837,393,740,360,954,990,794,378,327,741,468,516,
832,753,970,420,744,531,761,811,874,504,747,332,809,897,956,368,409,716,968,386,846,441,431,458,418,
961,479,507,505,367,454,523,425,872,804,981,964,328,362,861,927,841,765,423,939,326,766,714,405,344,
72,494,850,955,770,923,926,722,810,867,400,789,871,900,526,513,364,935,512,371,436,384,316,892,875,
988,906,374,895,911,844,798,745,342,319,330,760,983,478,978,783,973,986,868,467,855,853,388,449,773,
801,529,887,948,838,735,839,793,397,518,893,728,440,860,511,994,831,750,833,524,680,883,466,899,907,
375,933,985,942,414,486,905,894,739,462,885,940,357,514,443,749,754,946,947,725,343,473,452,807,931,
790,959,930,898,896,965,483,510,957,322,460,951,996,982,859,448,966,366,713,445,497,491,515,787,915,
743,470,972,932,944,489,485,732,525,758,772,379,806,325,411,976,805,755,395,752,389,958,995,417,902,
4,727,987,936,506,889,925,402,324,731,851,352,464,392,891,345,795,945,803,917,825,482,469,937,840,
434,383,496,488,450,828,377,774,391,706,934,484,522,729,819,842,490,908,422,763,757,826,920,916,350,
922,461,432,463,834,733,530,780,351,815,863,487,876,824,974,370,356,407,821,394,446,724,382,928,742,
465,817,528,778,969,341,372,999,849,711,442,730,428,857,869,993,759,918,406,710,862,949,796,878,919,
843,881,533,971,401,943,444,784,723,509,800,814,426,412,457,476,455,822,477,720,493,910,416,355,764,
410,421,984,782,736,707,776,471,989,456,903,886,317,914,430,802,827,779,880,399,953,459,962,904,519,
726,433,852,769,997,888,439,879,709,756,527,762,734,390,424,941,835,359,2,921,346,864,866,960,1,858,
719,369,532,979,413,358,323,398,534,873,447,321,403,495,348,991,913,823,797,820,816,847,385,865,376,
746,777,520,381,854,829,767,768,481,361,408,718,331,380,877,751,924,792,813,786,963,812,318,363,329,
929,3,535,427,992,909,508,437,938,320,998,419,901,517,349,856,808,480,870,737,785,453,890,952,451,
836,845,712,975,884,347,521,396,791,775,848,438,715,492,781,679,912,830,950,788,387,738,435,799,475,
429,415,967,373,721,882,717,708,837,393,740,360,954,990,794,378,327,741,468,516,832,753,970,420,744,
531,761,811,874,504,747,332,809,897,956,368,409,716,968,386,846,441,431,458,418,961,479,507,505,367,
454,523,425,872,804,981,964,328,362,861,927,841,765,423,939,326,766,714,405,344,472,494,850,955,770,
923,926,722,810,867,400,789,871,900,526,513,364,935,512,371,436,384,316,892,875,988,906,374,895,911,
844,798,745,342,319,330,760,983,478,978,783,973,986,868,467,855,853,388,449,773,801,529,887,948,838,
735,839,793,397,518,893,728,440,860,511,994,831,750,833,524,680,883,466,899,907,375,933,985,942,414,
486,905,894,739,462,885,940,357,514,443,749,754,946,947,725,343,473,452,807,931,790,959,930,898,896,
965,483,510,957,322,460,951,996,982,859,448,966,366,713,445,497,491,515,787,915,743,470,972,932,944,
489,485,732,525,758,772,379,806,325,411,976,805,755,395,752,389,958,995,417,902,4,727,987,936,506,
889,925,402,324,731,851,352,464,392,891,345,795,945,803,917,825,482,469,937,840,434,383,496,488,450,
828,377,774,391,706,934,484,522,729,819,842,490,908,422,763,757,826,920,916,350,922,461,432,463,834,
733,530,780,351,815,863,487,876,824,974,370,356,407,821,394,446,724,382,928,742,465,817,528,778,969,
341,372,999,849,711,442,730,428,857,869,993,759,918,406,710,862,949,796,878,919,843,881,533,971,401,
943,444,784,723,509,800,814,426,412,457,476,455,822,477,720,493,910,416,355,764,410,421,984,782,736,
707,776,471,989,456,903,886,317,914,430,802,827,779,880,399,953,459,962,904,519,726,433,852,769,997,
888,439,879,709,756,527,762,734,390,424,941,835,359,2,921,346,864,866,960,1,858,719,369,532,979,413,
358,323,398,534,873,447,321,403,495,348,991,913,823,797,820,816,847,385,865,376,746,777,520,381,854,
829,767,768,481,361,408,718,331,380,877,751,924,792,813,786,963,812,318,363,329,929,3,535,427,992,
909,508,437,938,320,998,419,901,517,349,856,808,480,870,737,785,453,890,952,451,836,845,712' ,',')

p4

Using the functions against data in a table

A reader asked how these functions can be used against data that exists in a table. Here is a simple example of populating a table and then using these functions:

CREATE TABLE #temp (id int, stringData varchar(100))
INSERT INTO #temp VALUES (1,'SQL Server 2000,SQL Server 2005,SQL Server 2008,SQL Server 7.0,SQL Server 2012,SQL Server 2008 R2')
INSERT INTO #temp VALUES (2,'Apple,Banana,Pear')


SELECT y.id, fn.string 
FROM #temp AS y CROSS APPLY dbo.fnParseStringTSQL(y.stringData, ',') AS fn


DROP TABLE #temp

resultset

Summary
With the above you can see the two different approaches to tackling this issue. For the most part you are probably fine with either code set, but do some testing to see which approach works best.

Next Steps
  • Give this example a try and see what other functions you could write that could take advantage of the CLR
  • If you have CLR functions that you want to share with the rest of the MSSQLTips.com community, please send them to [email protected] so we can post them for others to use.
  • If you don't know how to write either VB or C# now is the time to begin learning. You will find a lot of things these languages will make a lot easier to implement.
  • Do some testing and also take on other functions that you have written in T-SQL to see if they can run any faster in the CLR


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2013-01-14

Comments For This Article




Tuesday, March 20, 2018 - 11:54:56 AM - Greg Robidoux Back To Top (75482)

Hi Shafaeen,

you can just do the following:

CREATE TABLE test ( version varchar(200) )

INSERT INTO test
SELECT * FROM dbo.fnParseStringTSQL('SQL Server 2000|SQL Server 2005|SQL Server 2008|SQL Server 7.0','|')

 

Tuesday, March 20, 2018 - 11:03:07 AM - shafaeen Back To Top (75481)

Can you please let me know how to edit your code for taking as input a comma seperated flat file and giving output as table using clr function or procedure?

Monday, March 7, 2016 - 10:28:04 AM - Greg Robidoux Back To Top (40876)

Hi Jeff, thanks for taking the time to do some additional testing.  It is interesting to see that your T-SQL method was faster.  My T-SQL code above is a quick and dirty way of doing this and totattly not built for speed. 

By the way, what were your results running the CLR code on the same machine.  Just curious.

Thanks
Greg

 

Saturday, March 5, 2016 - 5:34:57 PM - Jeff Moden Back To Top (40868)

Hi, Greg,

Apologies for the late reply.  I've been having some problems with my email and, apparently, I missed your responce.

Also, thanks for all you do on this site.  I've use many ideas from your very well written tips for production work.

Shifting gears, thank you the corrections.  I'll give them a try.

As a bit of a sidebar, I had heard that the split function used in "Result = Input.Split(Separator)" is a bit slow and a bit of additional testing appears to bear that out.  Having previously proved that a CLR will beat it, I ran your final test code (the one that has "SELECT * FROM dbo.fnParseStringCLR" in it followed by a wad of numbers in a CSV) but replaced the CLR function with "DelimitedSplit8K" and here are the results I got (GRID mode return was on)

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 73 ms.

Thursday, December 3, 2015 - 6:57:27 AM - Greg Robidoux Back To Top (40190)

Hi Jeff, it turns out the code was messed up.  The < and > were changed to HTML tags so the entire code was not showing.

Sorry about that.  I just tried this on SQL 2014 with the corrected code above and this is working.

-Greg

Wednesday, December 2, 2015 - 10:40:01 PM - Jeff Moden Back To Top (40185)

Hi Greg,

First, thanks for the wonderful article.  I do have a problem implementing the code though.  When I go to create the CLR function on the SQL Server side, I get the following error.

The Init method for a CLR table-valued function must be annotated with SqlFunctionAttribute.

I'm pretty ignorant when it comes to VB.  Can you help please?

Thursday, October 17, 2013 - 4:28:02 PM - Ed Lyons Back To Top (27180)

Nice job Greg.  You're talents are much appreciated. Ed

Saturday, September 7, 2013 - 12:22:13 PM - Regan Wick Back To Top (26662)

Nevermind.  I see you are referencing a different post that used rCTE to create the tally table.  I just use a physical table.  

Tuesday, September 3, 2013 - 12:18:57 AM - Regan Wick Back To Top (26583)

@Jeff Moden

 

Hi Jeff- I don't see the rCTE nature of what I posted above (copied below). Can you please clarify?

 

 

CREATE FUNCTION[dbo].[fnListToTable8K](

@ListVARCHAR(8000)
,@DelimiterCHAR(1)
) RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
--this UNIONs a "1" (first element) with n+1 values (subsequent elements) relative to delimiter positions
--eliminates need to prepend a delimiter
WITH ElementStartCTE
AS
(
SELECT 1 AS Start
UNION ALL
SELECT
n+1
FROM
dbo.Numbers WITH (NOLOCK)
WHERE
n <= DATALENGTH(@List)
ANDSUBSTRING(@List,n,1) = @Delimiter
)

--ISNULL\NULLIF logic handles last element eliminating need to append delimiter

,ElementStartAndLengthCTE
AS
(
SELECT
Start
,ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,Start),0)-Start,DATALENGTH(@List)) AS Length
FROM
ElementStartCTE
)

SELECT

SUBSTRING(@List,Start,Length) AS Item
FROM
ElementStartAndLengthCTE;

 

Monday, July 1, 2013 - 10:12:35 PM - Jeff Moden Back To Top (25668)

@Steven Willis

Very nicely done, Mr. WIllis.  And thanks for the kudos.  Much appreciated.

Monday, July 1, 2013 - 10:09:53 PM - Jeff Moden Back To Top (25667)

@Regan Wick

First, thank you for the very kind words.  I very much appreciate it.

The method you're using for the function is an rCTE (recursive CTE) that counts.  While it looks very slick, rCTEs are "Hidden RBAR" on steroids.  Please see the following URL for more information on why you shouldn't use rCTEs that count (create a Tally-like sequence).

http://www.sqlservercentral.com/articles/T-SQL/74118/

 

Friday, June 7, 2013 - 8:58:28 AM - Greg Robidoux Back To Top (25334)

@Shariz - take a look at these tips on PIVOT

http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/

http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

Friday, June 7, 2013 - 3:22:01 AM - Shariz Back To Top (25328)

The above examples gets the values into different rows. How to get the all the values of one id into one row and different columns.

Like - 

Column1  Column2              Column3 Column4 Column5
1              SQL Server 2000  SQL Server 2005   SQL Server 2008  SQL Server 2012

Thursday, May 2, 2013 - 11:05:41 AM - shoham Back To Top (23686)

Thanks!!!

Thursday, January 17, 2013 - 2:06:58 PM - Steven Willis Back To Top (21530)

Also, for those needing an example of how to CROSS APPLY a tvf splitter function:

/***********************************************************************/

 

 

DECLARE 

    @InputString VARCHAR(8000)

   ,@Delimiter1 CHAR(1)

 

--this would be an example of a delimited string to be split

 

 

SET @InputString = '38469,38471,38474'

SET @Delimiter1 = ','

 

 

 

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

 

CREATE TABLE #TempTable (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [Col1] INT NULL,

    [Col2] NVARCHAR(50) NULL,

    PRIMARY KEY (ID))

 

--this is a sample data with keys to be matched against the values in the delimited string

INSERT INTO #TempTable

SELECT 38469,'Bob'

UNION

SELECT 38471,'George'

UNION

SELECT 38473,'John'

UNION

SELECT 38474,'Alice'

UNION

SELECT 38474,'Carol'

UNION

SELECT 38477,'Randy'

 

--this query uses a CROSS APPLY to match the table keys with the values in the delimited string

SELECT

    ID

   ,Col1

   ,Col2

FROM

    #TempTable AS tt

CROSS APPLY

    dbo.DelimitedSplit8K(@InputString,@Delimiter1) AS dsk

WHERE 

    tt.Col1 = dsk.Item

 
/***********************************************************************/
 
The output:
 
IDCol1Col2
138469Bob
238471George
438474Alice
538474Carol

 

 

 

Thursday, January 17, 2013 - 1:38:41 PM - Steven Willis Back To Top (21529)

In a cooperative effort over the last few years Jeff Moden over at SQLServerCentral has pretty much put this issue to rest. 

http://www.sqlservercentral.com/articles/Tally+Table/72993/ ... go to the bottom of the article and download all the code.

The function DelimitedSplit8K has been objectively performance tested against many other non-CLR versions. Jeff Moden even provides the testing script and procedures for anyone to use. The latest version of the function does not require an external numbers/tally table. I have personally tested this and competing versions of splitter functions using Jeff's test harness and DelimitedSplit8K cannot be beat without using a CLR.

By the way, Jeff also provides the code for a splitter CLR (and test results) and the CLR is by far the fastest splitter method. But if one is working on servers without full sysadmin control it is often not possible to build and run CLRs. Both functions produce exactly the same output columns so they can be used interchageably.

The DelimitedSplit8K function's only limitation is that the string being parsed must be be less than VARCHAR(8000) or NVARCHAR(4000). Splitting a value that requires a VARCHAR(MAX) datatype will kill performance and there are some other splitters that handle those rare situations better, though splitting such blobs will always be a performance problem. The function is also optimized for a single delimiter only with values of any size including random sizes. For strings with multiple delimiters (i.e., 2-dimenisonal) there is a variation of DelimitedSplit8K that has been shown to perform faster than trying to do multiple CROSS APPLYs with the same query. Details on that variation can be found here: http://www.sqlservercentral.com/Forums/FindPost1401102.aspx

DelimitedSplit8K is an iTVF and thus produces a virtual table as output. As in the example in a previous post the function can be used by using a JOIN or a CROSS APPLY.

Here is the code for anyone to use:

/***************************************************************************************/

CREATE FUNCTION [dbo].[DelimitedSplit8K]

--===== Define I/O parameters

       (

        @pString VARCHAR(8000)

       ,@pDelimiter CHAR(1)

       )

--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!

RETURNS TABLE

       WITH SCHEMABINDING

       AS

 RETURN

       --===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...

     -- enough to cover VARCHAR(8000)

  WITH  E1(N)

          AS (

              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

             ),                          --10E+1 or 10 rows

        E2(N)

          AS (

              SELECT

                1

              FROM

                E1 a

               ,E1 b

             ), --10E+2 or 100 rows

        E4(N)

          AS (

              SELECT

                1

              FROM

                E2 a

               ,E2 b

             ), --10E+4 or 10,000 rows max

        cteTally(N)

          AS (

              --==== This provides the "base" CTE and limits the number of rows right up front

                     -- for both a performance gain and prevention of accidental "overruns"

                 SELECT TOP (ISNULL(DATALENGTH(@pString),0))

                    ROW_NUMBER() OVER (ORDER BY (

                                                 SELECT NULL

                                                ))

                 FROM

                    E4

             ),

        cteStart(N1)

          AS (

              --==== This returns N+1 (starting position of each "element" just once for each delimiter)

                 SELECT

                    1

              UNION ALL

              SELECT

                t.N + 1

              FROM

                cteTally t

              WHERE

                SUBSTRING(@pString,t.N,1) = @pDelimiter

             ),

        cteLen(N1,L1)

          AS (

              --==== Return start and length (for use in substring)

                 SELECT

                    s.N1

                   ,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0) - s.N1,8000)

                 FROM

                    cteStart s

             )

       --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.

 SELECT

    ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)

   ,Item = SUBSTRING(@pString,l.N1,l.L1)

 FROM

    cteLen l ;

 

/***************************************************************************************/

 

 

 

Thursday, January 17, 2013 - 10:16:44 AM - Regan Wick Back To Top (21517)

There are faster T-SQL solution which use set-based processing. Some are detailed here: http://sqlrecords.blogspot.com/2012/11/converting-delimited-list-to-table.html

From Jeff Moden's efforts, this is the best performing UDF I have seen which uses a Numbers table

CREATE FUNCTION[dbo].[fnListToTable8K](

@ListVARCHAR(8000)
,@DelimiterCHAR(1)
) RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
--this UNIONs a "1" (first element) with n+1 values (subsequent elements) relative to delimiter positions
--eliminates need to prepend a delimiter
WITH ElementStartCTE
AS
(
SELECT 1 AS Start
UNION ALL
SELECT
n+1
FROM
dbo.Numbers WITH (NOLOCK)
WHERE
n <= DATALENGTH(@List)
ANDSUBSTRING(@List,n,1) = @Delimiter
)

--ISNULL\NULLIF logic handles last element eliminating need to append delimiter

,ElementStartAndLengthCTE
AS
(
SELECT
Start
,ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,Start),0)-Start,DATALENGTH(@List)) AS Length
FROM
ElementStartCTE
)

SELECT

SUBSTRING(@List,Start,Length) AS Item
FROM
ElementStartAndLengthCTE;

 

 

Tuesday, January 15, 2013 - 9:32:50 AM - Greg Robidoux Back To Top (21458)

@Venkat - thanks for your reply.  I have updated the tip based on your feedback.

Monday, January 14, 2013 - 4:04:38 PM - Venkat Back To Top (21442)

@Robert.. You have to create table valued function as mentioned in the post and then cross apply or outer apply (depends on your requirement) with your table with the column having delimited string.

Select (fn.Resultcolumn_function) From yourtable AS y CROSS APPLY tablevaluedfunction (y.delimitedstring, delimiter) AS fn

Saturday, June 30, 2012 - 12:40:10 PM - Robert Back To Top (18274)

As in most examples I have seen, this works great as long as you have a variable or supply a delimited string for it to work on.  I'm sure you know what RBAR means, and I'm trying to avoid that by diong set related processing, sin instead of typing in a string, what if I have a table with onle colum in it and 2000 entries of string text I want to process?  All the examples I have tried return one recors, I want to iterate (try not to use the word loop) through all 2000 entries and insert the results into a table, but I can't get to work, and there are no examples of this, but hundreds of examples on how to do just one string.

 

Robert.

Wednesday, March 21, 2012 - 8:41:14 AM - Paul Back To Top (16559)

Thanks so much for your script.  You saved me ages of time.

Wednesday, January 14, 2009 - 9:15:45 AM - admin Back To Top (2561)

Thanks for the alternate approaches.

Wednesday, January 14, 2009 - 3:37:29 AM - elsuket Back To Top (2560)

Hello,

You can also use CTEs under SQL Server 2005 to do the same thing.

The advantage is that you can specify a JOIN on a CTE :

DECLARE @str VARCHAR(64) 
SET @str = 'toto,titi,tutu,tata'; 
 
WITH CTE (Deb, Fin) AS 
( 
  SELECT 1 Deb, CHARINDEX(',', @str + ',') Fin 
 UNION ALL 
  SELECT Fin + 1, CHARINDEX(',', @str + ',', Fin + 1) 
  FROM CTE 
  WHERE CHARINDEX( ',', @str + ',', Fin + 1 ) > 0 
) 
SELECT SUBSTRING(@str, Deb , Fin - Deb), Deb, Fin 
FROM CTE 
 
-------------------------------------------
DECLARE @String VARCHAR(64) SET @String = 'un,deux,trois,quatre,cinq'; 
DECLARE @Separator CHAR(1) SET @Separator = ','; 
WITH INDICES AS 
( 
    SELECT 0 Deb, 1 Fin 
  UNION ALL 
    SELECT Fin, CHARINDEX(@Separator, @String, Fin) + LEN(@Separator) 
    FROM INDICES 
    WHERE Fin > Deb 
) 
SELECT SUBSTRING( 
          @String, 
          Deb, 
          CASE 
            WHEN Fin > LEN(@Separator) THEN Fin - Deb - LEN(@Separator) 
            ELSE LEN(@String) - Deb + 1 
          END 
        ) String, 
        Deb, 
        Fin, 
        LEN(@String) - Deb + 1 Lgr 
FROM INDICES WHERE Deb > 0















get free sql tips
agree to terms