SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Irfan Baig / String tokenizing / splitting / Latest Posts

RE: String tokenizing / splitting

Gatekeeper — Sat, 25 Feb 2012 19:35:32 GMT

[quote][b]Jeff Moden (2/25/2012)[/b][hr]Still, I have to give the credit to Itzik for the wonderful and very useful idea especially since it generates virtually no reads. The use of a physical Tally Table will still beat it by a bit in some applications but you simply have to love the "no reads" aspect of Itzik's fine idea.[/quote]Yes, the no reads is very hard to not pass up when thinking about tally tables, thanks to Itzik.

RE: String tokenizing / splitting

Jeff Moden — Sat, 25 Feb 2012 15:11:59 GMT

[quote][b]Gatekeeper (5/2/2011)[/b][hr]Not to mention his CTE tally table...[/quote]Thank you for the wonderful kudo but I absolutely cannot take the credit for the cteTally. The original concept was first published by Itzik Ben-Gan and he used a "base 2" generator. The "base 10" generator that I included in my article was the result of a lot of good people's (Lynn Petis started the ball rolling in this area) efforts here on SSC where we tested many different "bases". I chose to stick with the "base 10" generator because, as with many different based generators, it appeared to have a minor speed advantage over the "base 2" generator, took fewer Cross Joins to ramp up to the larger numbers and, as a result, took a bit less code.Still, I have to give the credit to Itzik for the wonderful and very useful idea especially since it generates virtually no reads. The use of a physical Tally Table will still beat it by a bit in some applications but you simply have to love the "no reads" aspect of Itzik's fine idea.

RE: String tokenizing / splitting

Gatekeeper — Mon, 02 May 2011 10:30:50 GMT

Jeff Moden posted a very nice article about CSV parsing. His code was written for a max input string of 8K so if you need more, stay tuned for an upcoming article from him. I tested his code vs mine and it completely blows it away. Not to mention his CTE tally table did much better than relying on a physical Tally table.[url=http://www.sqlservercentral.com/articles/Tally+Table/72993/]Tally Oh![/url]

RE: String tokenizing / splitting

Gatekeeper — Sat, 02 Apr 2011 23:28:08 GMT

As promised, I did some testing (sorry it took this long). This was run from my laptop in a Win2K3 Server VM. The results showed a pretty consistent run time of ~40 ms. Running the script in a loop of 20 times, the max ms were 50 and min was 30. The average was 40.45 and a median of 41. I will note that yes, there is a penalty for the first load of the tally table, which I saw the first time I ran it in tempdb. However, if it's used often, it will probably stay in memory, which it does in our system. But to be fair, I did a DBCC freeproccache and DBCC dropcleanbuffers between the tests below and did experience the occasional doubling of time you experienced but it wasn't consistent.Other changes made to your test script:1) The udf takes nvarchar so I changed chars to nchar to remove the implicit conversion2) Used datetime23) DATEDIFF for time difference calculation4) Added GO 20 to repeat the script 20 times[code="sql"]declare @i int = 26, @x nvarchar(max) = N'', @d nchar(1) = N' ', @j int;declare @t table (id tinyint primary key, tokens int, which nvarchar(32), start datetime2, finish datetime2);--Note: this is going to take a while, so if you want to run this more than once, store this data somewhere...set @j = @i*@i;while @j > 0 BEGINwhile @i > 0 BEGIN set @x = @x + @d +NCHAR(91 - @i); set @i = @i - 1;ENDset @j = @j - 1;set @i = 26ENDdeclare @c int;update @t set tokens = @c, finish = sysdatetime() where id = 1;insert into @t (id,which,start) values (2,'udf_StrList2Table',getdate());select @c = COUNT(*) from ..[udf_StrList2Table] (@x,@d)update @t set tokens = @c, finish = sysdatetime() where id = 2;select *, DATEDIFF(millisecond, start, finish) as runtime from @t;GO 20[/code]

RE: String tokenizing / splitting

davecason — Mon, 07 Mar 2011 22:17:41 GMT

I had a little more fun... again using 17,000+ tokens.I put the CTE version inside the original function by creating 200 character tokens using the original method (sort of), assuming a single character delimiter, then using CTE to further split them into arrays. It still has a few bugs but it did improve the overall results (hopefully, not due to the bugs):Original + CTE Hybrid: CPU time = 468 ms, elapsed time = 473 ms.Original: CPU time = 686 ms, elapsed time = 734 ms.Tally: CPU time = 172 ms, elapsed time = 157 ms.[code="sql"]CREATE FUNCTION [dbo].[fn_Split_Hybrid] ( @InputString VARCHAR(max), @Delimiter char(1)) RETURNS @Values TABLE ( VALUE VARCHAR(max) -- ,IPS VARCHAR(MAX) --,DCI BIGINT) AS BEGIN DECLARE @DelimitierLen tinyint = 200 DECLARE @DelimiterCharIndex bigint = @DelimitierLen --BIGINT = CHARINDEX(@Delimiter,@InputString) WHILE (@DelimiterCharIndex >= @DelimitierLen ) BEGIN declare @newval varchar(200) = left(@InputString, @DelimitierLen) SET @InputString = SUBSTRING(@InputString,@DelimitierLen+1, LEN(@InputString)-@DelimitierLen) SET @DelimiterCharIndex = LEN(@inputString) --INSERT INTO @Values VALUES (@newval)--, @InputString,@DelimiterCharIndex) BEGIN with split(i, token, remainder) as (select 1 , left(@newval,charindex(@Delimiter,@newval)-1) , LTRIM(right(@newval,len(@newval)-CHARINDEX(@Delimiter,@newval))) union all select i + 1 ,case when charindex(@Delimiter,remainder) > 0 then left(remainder,charindex(@Delimiter,remainder)-1) else remainder end as token ,LTRIM(right(remainder,len(remainder)-CHARINDEX(@Delimiter,remainder))) as remainder from split where charindex(@Delimiter,remainder) >= 0 and token != remainder ) insert into @Values Select token from split END END set @newval = @InputString BEGIN with split(i, token, remainder) as (select 1 , left(@newval,charindex(@Delimiter,@newval)-1) , LTRIM(right(@newval,len(@newval)-CHARINDEX(@Delimiter,@newval))) union all select i + 1 ,case when charindex(@Delimiter,remainder) > 0 then left(remainder,charindex(@Delimiter,remainder)-1) else remainder end as token ,LTRIM(right(remainder,len(remainder)-CHARINDEX(@Delimiter,remainder))) as remainder from split where charindex(@Delimiter,remainder) >= 0 and token != remainder ) insert into @Values Select token from split END RETURN ENDGO[/code]

RE: String tokenizing / splitting

davecason — Mon, 07 Mar 2011 19:40:09 GMT

My theory on the XML problem was that it was actually a CLR call-out. Tally seems to work well, it shows an up front execution cost that is not comparable to the others, but it seems to perform better.I'm going to try to tweak each idea a bit and put them under more competition scenarios to see which ones fare better at scale with parallel use.

RE: String tokenizing / splitting

Gatekeeper — Mon, 07 Mar 2011 07:54:39 GMT

[quote][b]Craig Sunderland (3/7/2011)[/b][hr]Surely other factors like CPU, Memory and Disk Configuration could affect these timings, hence why from my tests the XML was a better fit. There probably isn't one size fits all.[/quote]Yes, it would skew the tests from one machine to the other but he's been testing on the same machine and instance. If the results widely vary, then it shows it doesn't scale well. However, the three ideas tested has their own disadvantages. One requires CLR procs to be enabled, another needs a tally table, and the other works well with smaller delimited lists. If you plan to never have a list of hundreds or thousands, XML will work without any other object. But if you need to have that flexibility, it's not a big performance hit to use the tally function for smaller sets too. Testing and re-visiting those tests from time to time will let you know if you're still using the best function for your current hardware/data distribution.

RE: String tokenizing / splitting

Craig Sunderland — Mon, 07 Mar 2011 02:34:13 GMT

Surely other factors like CPU, Memory and Disk Configuration could affect these timings, hence why from my tests the XML was a better fit. There probably isn't one size fits all.

RE: String tokenizing / splitting

davecason — Sat, 05 Mar 2011 14:47:05 GMT

I ran the three types again, with "SET EXECUTION TIME ON" and......8788 tokens:XML: CPU time = 203722 msTally: CPU time = 47 msOriginal: CPU time = 686 ms

RE: String tokenizing / splitting

davecason — Fri, 04 Mar 2011 20:52:16 GMT

That function looks pretty similar to the one from the original article, which I called "fn_Split". It does fine, too, and it will work on older versions of SQL Server.For joining, there are a couple of basic ways to do it. The tough part can be when you want to pass the table name involved as a variable, as you either you have to do some SQL-Server-2008-only stuff using a newly declared type, or you have to use string concatenation with EXEC, so be careful with that one regarding SQL Injection... AND some of the XML functionality might let you find a back door, too. The basic premise for joining can use either CTE (as I wrote in my very first blog post) or a COALESCE() or perhaps a CURSOR (which will be a little slow). Here is a quick and dirty example using a table variable and a 2008-only insert statement to fill that test table variable, and then a COALESCE() to join all the rows in that table:[code="sql"]DECLARE @table TABLE (token VARCHAR(max));INSERT INTO @table VALUES ('xyz'),('pdq'),('abc'),('123')DECLARE @joined VARCHAR(max)SELECT @joined=COALESCE(@joined+',', '')+token FROM @tableSELECT @joined[/code]

RE: String tokenizing / splitting

bCoderer — Thu, 03 Mar 2011 04:44:13 GMT

Hey guys,look at here --> [url=http://blog.sqlauthority.com/2007/05/06/sql-server-udf-function-to-convert-list-to-table/]udf_ListToTable[/url]maybe you could run it within your performance tests,NorbertPS: For the way upsidedown take this --> [url=http://blog.sqlauthority.com/2007/05/06/sql-server-creating-comma-separate-values-list-from-table-udf-sp/]udf_TableToList[/url]

RE: String tokenizing / splitting

Gatekeeper — Wed, 02 Mar 2011 22:21:08 GMT

Interesting about the varying times with smaller sets of data. I'll do some testing and see what I can find.

RE: String tokenizing / splitting

davecason — Wed, 02 Mar 2011 21:26:11 GMT

The tally table method seems to work fine, but the estimated and actual execution plans show a pretty severe penalty. My guess is that the initial file I/O and memory allocation of the table's index are at fault for those costs. I then derived a little test on about 17,000 tokens using the initial method, the XML method, and the tally table. With that said, the tally table won by a long shot when parsing a large array:[code="sql"]declare @i int = 26, @x varchar(max) = '', @d char(1) = ' ', @j int;declare @t table (id tinyint primary key, tokens int, which varchar(32), start datetime, finish datetime);--Note: this is going to take a while, so if you want to run this more than once, store this data somewhere...set @j = @i*@i;while @j > 0 BEGINwhile @i > 0 BEGIN set @x = @x + @d +CHAR(91 - @i); set @i = @i - 1;ENDset @j = @j - 1;set @i = 26ENDdeclare @c int;insert into @t (id,which,start) values (1,'fnStringSplit_2005_Distinct_CHAR',getdate());select @c = COUNT(*) from ..[fnStringSplit_2005_Distinct_CHAR](@x,@d);update @t set tokens = @c, finish = GETDATE() where id = 1;insert into @t (id,which,start) values (2,'udf_StrList2Table',getdate());select @c = COUNT(*) from ..[udf_StrList2Table] (@x,@d)update @t set tokens = @c, finish = GETDATE() where id = 2;insert into @t (id,which,start) values (3,'fn_Split',getdate());select @c = COUNT(*) from ..[fn_Split](@x,@d)update @t set tokens = @c, finish = GETDATE() where id = 3;select *, CONVERT(float, finish) - convert(float,start) as runtime from @t;[/code]The results show us that the clear winner as the tally table. Volume seems to be a very large consideration in which method you choose. It looks like arrays of enormous scale should be parsed with a tally table method:id tokens which start finish runtime2 17577 udf_StrList2Table 54:52.0 54:52.0 0.000000578700564801693 17577 fn_Split 54:52.0 55:04.0 0.000138541669002734001 17577 fnStringSplit_2005_Distinct_CHAR 51:26.9 54:52.0 0.00237364969507325000I added back my CTE function and reran it with a limit of 100 tokens (CTE max):[code="sql"]declare @i int = 26, @x varchar(max) = '', @d char(1) = ' ', @j int;declare @t table (id tinyint primary key, tokens int, which varchar(32), start datetime, finish datetime);set @j = @i;while @j > 0 BEGINwhile @i > 0 BEGIN set @x = @x + @d +CHAR(91 - @i); set @i = @i - 1;ENDset @j = @j - 1;set @i = 26ENDset @x = LEFT(@x,100)declare @c int;insert into @t (id,which,start) values (0,'fnStringSplit_CTE',getdate());select @c = COUNT(*) from ..[fnStringSplit_CTE](@x,@d)update @t set tokens = @c, finish = GETDATE() where id = 0;insert into @t (id,which,start) values (1,'fnStringSplit_2005_Distinct_CHAR',getdate());select @c = COUNT(*) from ..[fnStringSplit_2005_Distinct_CHAR](@x,@d);update @t set tokens = @c, finish = GETDATE() where id = 1;insert into @t (id,which,start) values (2,'udf_StrList2Table',getdate());select @c = COUNT(*) from ..[udf_StrList2Table] (@x,@d)update @t set tokens = @c, finish = GETDATE() where id = 2;insert into @t (id,which,start) values (3,'fn_Split',getdate());select @c = COUNT(*) from ..[fn_Split](@x,@d)update @t set tokens = @c, finish = GETDATE() where id = 3;select *, CONVERT(float, finish) - convert(float,start) as runtime from @t;[/code]The results show CTE is the winner, but the others are fairly close with the tally table last:id tokens which start finish runtime0 51 fnStringSplit_CTE 10:29.7 10:29.7 0.000000038584403228011 51 fnStringSplit_2005_Distinct_CHAR 10:29.7 10:29.7 0.000000270061718765653 51 fn_Split 10:29.7 10:29.8 0.000000270061718765652 51 udf_StrList2Table 10:29.7 10:29.7 0.00000038580037653446The funny part about this one is that if I ran it a dozen times, only the tally table's result changed dramatically when splitting a small array of values (it sometimes took a lot longer... randomly).So what I take from this is CTE is best for small arrays, and the tally table is best for large ones. The one last test I did not try is to run several thousand small splits at volume (may about 200 sets of 100 would do it). The only way I could think of to mock this up is to make a highly parallel package call via SSIS (basically, the same function called 2000 times at the same time). If I feel inspired, I'll give it a shot. Any suggestions on an easier parallelization test are welcome.

RE: String tokenizing / splitting

Gatekeeper — Wed, 02 Mar 2011 12:20:52 GMT

We've found good use of the following tally table and UDF. It handles empty elements and works fast for us. We've done large and small sets against it.[code="sql"] CREATE TABLE dbo.Tally (N INT) -- Default Data-- Taken from the following website:-- http://www.sqlservercentral.com/articles/TSQL/62867/--=============================================================================-- Create and populate a Tally table--=============================================================================--===== Conditionally drop IF OBJECT_ID('dbo.Tally') IS NOT NULL DROP TABLE dbo.Tally--===== Create and populate the Tally table on the fly SELECT TOP 3000000 --equates to more than 30 years of dates IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2--===== Add a Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100--===== Let the public use it GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLICGOCREATE FUNCTION udf_StrList2Table ( @List NVARCHAR(MAX) = N'', @Delimiter NCHAR(1) = N',')RETURNS TABLEASRETURN SELECT SUBSTRING( @Delimiter + @List + @Delimiter, n + 1, CHARINDEX( @Delimiter, @Delimiter + @List + @Delimiter, n + 1 ) - n - 1 ) AS Value, ROW_NUMBER() OVER ( ORDER BY n ) AS ListPos FROM Tally WHERE SUBSTRING( @Delimiter + @List + @Delimiter, n, 1 ) = @Delimiter AND n < CAST(LEN( @Delimiter + @List + @Delimiter ) as int)[/code]

RE: String tokenizing / splitting

davecason — Mon, 28 Feb 2011 22:38:31 GMT

Norbert, try to fix it the way you want it, and post your code if you get stuck. I intentionally attempted to keep the last token if the delimiter was followed by nothing.

RE: String tokenizing / splitting

irfibaig — Mon, 28 Feb 2011 04:19:32 GMT

Dear in your first scenario it being returned 3 rows and in second scenario that will be returned 4 rows according to delimiter counting e.g. ‘bla;bla;bla;’ the last row will be NULL. You can handle it in your code where you want to use this function.

RE: String tokenizing / splitting

bCoderer — Mon, 28 Feb 2011 03:32:55 GMT

Hi,@davecasonYour function does only return 2 rows if there is a string like that given: 'bla;bla;bla'. And if a string like this is given 'bla;bla;bla;' 4 rows are returned. Could you correct that?regardsNorbert

RE: String tokenizing / splitting

davecason — Fri, 25 Feb 2011 19:33:53 GMT

CTE version, made more like the SUBSTRING version:[code="sql"]CREATE FUNCTION [dbo].[fnStringSplit_CTE] (@SourceString VARCHAR(MAX),@Delim CHAR(1))RETURNS @Values TABLE(VALUE VARCHAR(MAX) )ASBEGINwith split(i, token, remainder) as(select 1, left(@SourceString,charindex(@delim,@SourceString)-1), LTRIM(right(@SourceString,len(@SourceString)-CHARINDEX(@delim,@SourceString)))union allselect i + 1,case when charindex(@delim,remainder) > 0 then left(remainder,charindex(@delim,remainder)-1) else remainder end as token,LTRIM(right(remainder,len(remainder)-CHARINDEX(@Delim,remainder))) as remainderfrom splitwhere charindex(@delim,remainder) >= 0 and token != remainder )insert into @ValuesSelect tokenfrom splitRETURNENDGO[/code]

RE: String tokenizing / splitting

davecason — Fri, 25 Feb 2011 19:31:59 GMT

I ran the XML, CTE, and the SUBSTRING functions through and checked the estimated to actual execution plans. The estimated shows the XML_Reader is, by far, the most costly (so costly it can't compare). The Substring shows to be twice as expensive as the CTE, but CTE only works (naturally) up to 100 tokens. The actual came out as equal, so we'd have to design something to check the execution at scale... which eliminates the CTE version unless the test is designed using parallel effort rather sheer token volume.You could design a SUBSTRING/CTE hybrid where CTE is executed with a try/catch, then cut to the SUBSTRING if it fails by volume... but it might be simply smarter to use the version that fits your use case the best. The SUBSTRING version has a bug where if the delimiter is a space or any phrase with a trailing space, it trims off that space (or ignores it) and comes up one character short. For VARCHAR, you can fix this by using the DATALENGTH() function instead of LEN() and for NVARCHAR, use DATALENGTH(), then divide by two.I also found a bug in the XML version that used a clustered index on the returned table. That will change the token order and require unique tokens. You can remove the index to avoid natural reordering by the indexer and errors raised by duplicate tokens. Avoiding a tablescan here may or may not make sense for your use case.I also forgot to mention the TALLY version. It uses CTE, too, and it was slightly more expensive than the SUBSTRING version so I didn't bother comparisons past that point because it had the worst of both CTE and SUBSTRING versions.One more bug: the XML version does not handle special characters such as the ampersand(&). The use of a CDATA declaration may help but I did not try it.

RE: String tokenizing / splitting

nadabadan — Fri, 25 Feb 2011 17:01:49 GMT

[quote][b]dimitri.decoene-1027745 (2/25/2011)[/b][hr]Another way to do it is by using the Tally table. Check out Jeff Moden's article [url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url].Here's some example code (how i use it on our systems):[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[udfTransformStringToColumn] ( @String VARCHAR(MAX), @Delimiter VARCHAR(1))RETURNS @List TABLE (ValueColumn VARCHAR(255) NULL)ASBEGIN IF LEFT(@String, 1) <> @Delimiter SET @String = @Delimiter + @String IF RIGHT(@String, 1) <> @Delimiter SET @String = @String + @Delimiter ;WITH cteTally AS ( SELECT TOP (LEN(@String)) ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N FROM Master.sys.All_Columns t1 CROSS JOIN Master.sys.All_Columns t2 ) INSERT INTO @List (ValueColumn) SELECT SUBSTRING(@String,N+1,CHARINDEX(@Delimiter,@String,N+1)-N-1) AS Value FROM cteTally WHERE N < LEN(@String) AND SUBSTRING(@String,N,1) = @Delimiter RETURNENDGO[/code][/quote]Interesting function. However, you may want to always append a delimiter to the string because the function would not return the correct number of beginning empty strings. ChangeIF LEFT(@String, 1) <> @Delimiter SET @String = @Delimiter + @StringTO SET @String = @Delimiter + @String[code="plain"]SELECT *FROM [dbo].[udfTransformStringToColumn]( ';;;Token1;Token2;Token3;Token4;Token5;;', ';' )[/code]The beginning ";;;" of the string should return 3 blank rows, however, the function returns 2.

RE: String tokenizing / splitting

gorr-688214 — Fri, 25 Feb 2011 13:46:16 GMT

[code="sql"]CREATE FUNCTION [dbo].[Split](@data VARCHAR(MAX), @delimiter VARCHAR(20))RETURNS @t TABLE (Element VARCHAR(MAX))ASBEGIN DECLARE @textXML XML SET @textXML = CAST('<d>' + REPLACE(REPLACE(REPLACE(@data,'&','~~amper~~'),'<','~~lt~~'), @delimiter, '</d><d>') + '</d>' AS XML) INSERT INTO @t(element) SELECT REPLACE(REPLACE(T.split.value('.', 'varchar(max)'),'~~amper~~','&'), '~~lt~~','<') AS data FROM @textXML.nodes('/d') T(split) RETURNEND[/code]

RE: String tokenizing / splitting

AAYakovenko — Fri, 25 Feb 2011 03:14:10 GMT

Fast, but not good. ([code="sql"]DECLARE @inputString varchar(MAX) = '1;2;4;5;'[/code]Result of exec is 'XML parsing: line 1, character 97257, unexpected end of input'.You need check input string.

RE: String tokenizing / splitting

AAYakovenko — Fri, 25 Feb 2011 02:54:01 GMT

Small Code for 2008 SQL. )[code="sql"]CREATE FUNCTION dbo.fn_Split ( @InputString VARCHAR(MAX), @Delimiter VARCHAR(MAX)) RETURNS @Data TABLE ( Data VARCHAR(MAX)) AS BEGIN DECLARE @DelimitierLen BIGINT = LEN(@Delimiter) DECLARE @DelimiterCharIndex BIGINT = CHARINDEX(@Delimiter,@InputString) WHILE (@DelimiterCharIndex > 0) BEGIN INSERT INTO @Data VALUES (SUBSTRING(@InputString, 1, @DelimiterCharIndex - 1)) SET @InputString = SUBSTRING(@InputString, @DelimiterCharIndex + @DelimitierLen, LEN(@InputString)) SET @DelimiterCharIndex = CHARINDEX(@Delimiter, @InputString) END INSERT INTO @Data VALUES (@InputString) RETURN END[/code]

RE: String tokenizing / splitting

Craig Sunderland — Fri, 25 Feb 2011 01:20:55 GMT

I find the below faster on 2005.[size="1"][font="Courier New"]CREATE FUNCTION [Strings].[fnStringSplit_2005_Distinct_CHAR] ( @SourceString VARCHAR(MAX))/*======================================================================================'P System : Multiple'P Subsystem : Common Functions'P Script : fnSMS_StringSplit_2005'P Creation date : 15/10/2010'P'P Description : Splits a Comma Delimited String Into a Table. Join as a Table'P'P SELECT * FROM Strings.fnStringSplit_2005_Distinct_CHAR('9,8,7,6,5,4,3,2,1,')'P'P Parameters ----------------------------------------------------------------------'P Inputs : @SourceString - Comma delimited string'P Outputs : table variable 'P====================================================================================*/RETURNS @Values TABLE( --POSITION INT IDENTITY, VALUE VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY ([VALUE]))ASBEGIN DECLARE @vchString VARCHAR(MAX) DECLARE @xmlString XML BEGIN SET @vchString = @SourceString IF RIGHT(@vchString,1) = ',' SET @vchString = LEFT(@vchString, LEN(@vchString)-1) /*------------------------------------------ | Convert the string to xml '-----------------------------------------*/ SET @xmlString = CAST('' + REPLACE(@vchString, ',', '') + '' AS XML) /*------------------------------------------ | Read xml into a table variable '-----------------------------------------*/ INSERT INTO @Values(VALUE) SELECT DISTINCT x.i.value('.', 'VARCHAR(255)') AS Item FROM @xmlString.nodes('//i') x(i) END RETURNEND/*---------------------------------------------------------------------------------------|--------------------------- End of common function -----------------------------||--------------------------------------------------------------------------------------*/[/font][/size]

RE: String tokenizing / splitting

dimitri.decoene-1027745 — Fri, 25 Feb 2011 01:09:33 GMT

Another way to do it is by using the Tally table. Check out Jeff Moden's article [url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url].Here's some example code (how i use it on our systems):[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[udfTransformStringToColumn] ( @String VARCHAR(MAX), @Delimiter VARCHAR(1))RETURNS @List TABLE (ValueColumn VARCHAR(255) NULL)ASBEGIN IF LEFT(@String, 1) <> @Delimiter SET @String = @Delimiter + @String IF RIGHT(@String, 1) <> @Delimiter SET @String = @String + @Delimiter ;WITH cteTally AS ( SELECT TOP (LEN(@String)) ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N FROM Master.sys.All_Columns t1 CROSS JOIN Master.sys.All_Columns t2 ) INSERT INTO @List (ValueColumn) SELECT SUBSTRING(@String,N+1,CHARINDEX(@Delimiter,@String,N+1)-N-1) AS Value FROM cteTally WHERE N < LEN(@String) AND SUBSTRING(@String,N,1) = @Delimiter RETURNENDGO[/code]

RE: String tokenizing / splitting

davecason — Fri, 25 Feb 2011 00:36:06 GMT

I am fairly certain that CTE will be faster...[left]/*sql server 2008 CTE split and join fun*//*split begins*/if object_id(N'tempdb..#split') is not null drop table #split;if object_id(N'tempdb..#joined') is not null drop table #joined;declare @fun varchar(64) = 'The quick brown fox jumped over the lazy dogs!';declare @delim char(1) = ' ';select @fun as [Fun];with split(i, token, remainder) as(select 1, left(@fun,charindex(@delim,@fun)-1), LTRIM(right(@fun,len(@fun)-CHARINDEX(@delim,@fun)))union allselect i + 1,case when charindex(@delim,remainder) > 0 then left(remainder,charindex(@delim,remainder)-1) else remainder end as token,LTRIM(right(remainder,len(remainder)-CHARINDEX(' ',remainder))) as remainderfrom splitwhere charindex(@delim,remainder) >= 0 and token != remainder )select i, token, remainderinto #splitfrom split;select * from #split;/*join begins*/with joined (i, newfun, token) as (select i, convert(varchar(max),token), token from #split where i = 1union allselect s.i, j.newfun + @delim + s.token, s.token from joined jinner join#split son s.i = j.i + 1)select * into #joinedfrom joined;select * from #joined; [/left]

String tokenizing / splitting

irfibaig — Thu, 24 Feb 2011 20:56:41 GMT

Comments posted to this topic are about the item [B]String tokenizing / splitting[/B]