April 13, 2014 at 11:34 am
Miller (4/13/2014)
Thanks Jeff,I think you forgot to add the link in your quote.
Not sure what happened but I've repaired the link. Here it is, again.
http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/
--Jeff Moden
Change is inevitable... Change for the better is not.
April 13, 2014 at 12:31 pm
I made a few improvements: handles space delimiters now. Also added DISTINCT so you can use in JOINS
CREATE FUNCTION [dbo].[BetterSplit]
(@L NVARCHAR(MAX),@D NVARCHAR(100))
RETURNS TABLE AS RETURN
WITH A(N)AS(SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1)
,B(N)AS(SELECT 1FROM A,A B),C(N)AS(SELECT 1FROM B A,B),D(N)AS(SELECT 1FROM C A,C)
,T(N)AS(SELECT TOP(LEN(@L))ROW_NUMBER()OVER(ORDER BY(SELECT 1))FROM D)
,S AS(SELECT S=1UNION ALL SELECT S=N+DATALENGTH(@D)/2FROM T WHERE SUBSTRING(@L,N,DATALENGTH(@D)/2)=@D)
,L AS(SELECT S,L=LEAD(S,1,LEN(@L)+DATALENGTH(@D)/2+1)OVER(ORDER BY S)-S-DATALENGTH(@D)/2FROM S)
SELECT DISTINCT Value=LTRIM(RTRIM(SUBSTRING(@L,S,L)))FROM L
April 14, 2014 at 3:26 am
Miller (4/13/2014)
I made a few improvements: handles space delimiters now. Also added DISTINCT so you can use in JOINSCREATE FUNCTION [dbo].[BetterSplit]
(@L NVARCHAR(MAX),@D NVARCHAR(100))
RETURNS TABLE AS RETURN
WITH A(N)AS(SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1)
,B(N)AS(SELECT 1FROM A,A B),C(N)AS(SELECT 1FROM B A,B),D(N)AS(SELECT 1FROM C A,C)
,T(N)AS(SELECT TOP(LEN(@L))ROW_NUMBER()OVER(ORDER BY(SELECT 1))FROM D)
,S AS(SELECT S=1UNION ALL SELECT S=N+DATALENGTH(@D)/2FROM T WHERE SUBSTRING(@L,N,DATALENGTH(@D)/2)=@D)
,L AS(SELECT S,L=LEAD(S,1,LEN(@L)+DATALENGTH(@D)/2+1)OVER(ORDER BY S)-S-DATALENGTH(@D)/2FROM S)
SELECT DISTINCT Value=LTRIM(RTRIM(SUBSTRING(@L,S,L)))FROM L
Jeff’s “Tally Oh!” article and this discussion thread are a brilliant example of continuous improvement – incremental changes, tested and peer-reviewed. An excellent example of this is Eirikur’s recent performance-boosting modification using LEAD(). It’s been rigorously tested to ensure it meets requirements and also meets the claims made – that it’s significantly faster than the original. It does, and it is.
“BetterSplit” is perhaps a little optimistic. It fails if the delimiter has a trailing space
SELECT * FROM [dbo].[BetterSplit] ('the; quick; ,brown; fox; jumped; over; the; lazy; dog; ', '; ')
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
Resolved elements have leading spaces stripped. The ordinal position of elements within the string is discarded and the elements are re-ordered alphabetically by a distinct sort which adds considerably to the cost of the function. Apparently it hasn’t been performance-tested since MAX strings are known to spoil performance. Finally, DelimitedSplit8K does handle a single space as a delimiter – and correctly resolves a string with a terminal space delimiter, which “BetterSplit” does not:
SELECT DISTINCT Item FROM [dbo].[DelimitedSplit8K] ('the quick brown fox jumped over the lazy dog ', ' ')
?9 rows returned
SELECT * FROM [dbo].[BetterSplit] ('the quick brown fox jumped over the lazy dog ', ' ')
?8 rows returned
Whilst there are always going to be edge cases which are better handled by modifications to DelimitedSplit8K than by the original function, it’s important to define exactly why such a modification might be better and for exactly what purpose. Returning DISTINCT results from the function is probably insufficient as it’s trivial to return distinct results from DelimitedSplit8K. Multi-character delimiters is certainly worth investigation as a desirable modification, once the bugs (and the performance) in “BetterSplit” have been fixed.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 14, 2014 at 7:16 am
Yes, Thanks for the test. I fixed the problem noted. Had to use DATALENGTH throughout instead of LEN to handle trailing spaces.
SELECT * FROM [dbo].[BetterSplit] ('the; quick; ,brown; fox; jumped; over; the; lazy; dog; ', '; ')
WORKS NOW.
CREATE FUNCTION [dbo].[BetterSplit]
(@L NVARCHAR(MAX),@D NVARCHAR(100))
RETURNS TABLE AS RETURN
WITH A(N)AS(SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1)
,B(N)AS(SELECT 1FROM A,A B),C(N)AS(SELECT 1FROM B A,B),D(N)AS(SELECT 1FROM C A,C)
,T(N)AS(SELECT TOP(DATALENGTH(@L)/2)ROW_NUMBER()OVER(ORDER BY(SELECT 1))FROM D)
,S AS(SELECT S=1UNION ALL SELECT S=N+DATALENGTH(@D)/2FROM T WHERE SUBSTRING(@L,N,DATALENGTH(@D)/2)=@D)
,L AS(SELECT S,L=LEAD(S,1,DATALENGTH(@L)/2+DATALENGTH(@D)/2+1)OVER(ORDER BY S)-S-DATALENGTH(@D)/2FROM S)
SELECT Value=SUBSTRING(@L,S,L)FROM L
April 14, 2014 at 8:34 am
Miller (4/14/2014)
Yes, Thanks for the test. I fixed the problem noted. Had to use DATALENGTH throughout instead of LEN to handle trailing spaces.SELECT * FROM [dbo].[BetterSplit] ('the; quick; ,brown; fox; jumped; over; the; lazy; dog; ', '; ')
WORKS NOW.
CREATE FUNCTION [dbo].[BetterSplit]
(@L NVARCHAR(MAX),@D NVARCHAR(100))
RETURNS TABLE AS RETURN
WITH A(N)AS(SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1UNION ALL SELECT 1)
,B(N)AS(SELECT 1FROM A,A B),C(N)AS(SELECT 1FROM B A,B),D(N)AS(SELECT 1FROM C A,C)
,T(N)AS(SELECT TOP(DATALENGTH(@L)/2)ROW_NUMBER()OVER(ORDER BY(SELECT 1))FROM D)
,S AS(SELECT S=1UNION ALL SELECT S=N+DATALENGTH(@D)/2FROM T WHERE SUBSTRING(@L,N,DATALENGTH(@D)/2)=@D)
,L AS(SELECT S,L=LEAD(S,1,DATALENGTH(@L)/2+DATALENGTH(@D)/2+1)OVER(ORDER BY S)-S-DATALENGTH(@D)/2FROM S)
SELECT Value=SUBSTRING(@L,S,L)FROM L
Now, you only need to do two more things. One format your code for readability. Two, change your CTE to use the value constructor instead of SELECT 1 UNION's. The latter only makes sense since you are using SQL Server 2012 features in the code anyway.
April 14, 2014 at 9:39 am
How about this? I'd be curious to see results of performance testing with this.
CREATE FUNCTION [dbo].[BetterSplit]
(
@L NVARCHAR(MAX)
,@D NVARCHAR(100)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
--A,B,C,D used to generate large number of rows for tally
A AS(SELECT N FROM(VALUES(1),(1),(1),(1),(1),(1))A(N))
,B(N)AS(SELECT 1FROM A,A B)
,C(N)AS(SELECT 1FROM B A,B)
,D(N)AS(SELECT 1FROM C A,C)
--Tally table
,T(N)AS(SELECT TOP(DATALENGTH(@L)/2)ROW_NUMBER()OVER(ORDER BY(SELECT 1))FROM D)
--Start of each delimiter
,S AS(SELECT S=1UNION ALL SELECT S=N+DATALENGTH(@D)/2FROM T WHERE SUBSTRING(@L,N,DATALENGTH(@D)/2)=@D)
--Start, Length of each value in list.
--Use LEAD() to get next row start, then subtract current row start and adjust for size of delimiters
,L AS(SELECT S,L=LEAD(S,1,DATALENGTH(@L)/2+DATALENGTH(@D)/2+1)OVER(ORDER BY S)-S-DATALENGTH(@D)/2FROM S)
--do final select here based on start, length
SELECT Value=SUBSTRING(@L,S,L)FROM L
April 14, 2014 at 9:55 am
Miller (4/14/2014)
How about this? I'd be curious to see results of performance testing with this.CREATE FUNCTION [dbo].[BetterSplit]
(
@L NVARCHAR(MAX)
,@D NVARCHAR(100)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
--A,B,C,D used to generate large number of rows for tally
A AS(SELECT N FROM(VALUES(1),(1),(1),(1),(1),(1))A(N))
,B(N)AS(SELECT 1FROM A,A B)
,C(N)AS(SELECT 1FROM B A,B)
,D(N)AS(SELECT 1FROM C A,C)
--Tally table
,T(N)AS(SELECT TOP(DATALENGTH(@L)/2)ROW_NUMBER()OVER(ORDER BY(SELECT 1))FROM D)
--Start of each delimiter
,S AS(SELECT S=1UNION ALL SELECT S=N+DATALENGTH(@D)/2FROM T WHERE SUBSTRING(@L,N,DATALENGTH(@D)/2)=@D)
--Start, Length of each value in list.
--Use LEAD() to get next row start, then subtract current row start and adjust for size of delimiters
,L AS(SELECT S,L=LEAD(S,1,DATALENGTH(@L)/2+DATALENGTH(@D)/2+1)OVER(ORDER BY S)-S-DATALENGTH(@D)/2FROM S)
--do final select here based on start, length
SELECT Value=SUBSTRING(@L,S,L)FROM L
Then tell us. If you've taken the time to read the article, you will know that it includes a full test harness.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 14, 2014 at 12:20 pm
OK, I ran the splitter tests.
I had to modify the BetterSplit a little: Switched to varchar(8000) instead of nvarchar(max) for list parameter. This needed to get good comparison between other algorithms using these datatypes. Nvarchar(max) is much slower but how else can you get more than 8000 characters?
Also added ItemNumber to result, even though in my usage the ItemNumber is not very useful.
Also I increased the first Cte to 10 items and eliminated the 4th Cte. This results in fewer nested loops.
I didn't bother taking out the logic to handle multi-character delimiters (it might be faster without this)
Anyway, after all this I ended up with BetterSplit being about 11% faster than the DelimitedSplit8K.
Not much, but I think it shows that the LEAD() function must be fundamentally faster than the CHARINDEX() for some reason. Everything else is about the same.
IF OBJECT_ID('dbo.[BetterSplit]') IS NOT NULL
DROP FUNCTION dbo.BetterSplit;
GO
create FUNCTION [dbo].[BetterSplit]
(
@L VARCHAR(8000)
,@D VARCHAR(10)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
--A,B,C used to generate large number of rows for tally
A AS(SELECT N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))A(N))
,B(N)AS(SELECT 1FROM A,A B)
,C(N)AS(SELECT 1FROM B A,B)
--Tally table
,T(N)AS(SELECT TOP(ISNULL(DATALENGTH(@L),0))ROW_NUMBER()OVER(ORDER BY(SELECT 1))FROM C)
--Start of each delimiter
,S AS(SELECT S=1UNION ALL SELECT S=N+DATALENGTH(@D)FROM T WHERE SUBSTRING(@L,N,DATALENGTH(@D))=@D)
--Start, Length of each value in list.
--Use LEAD() to get next row start, then subtract current row start and adjust for size of delimiters
,L AS(SELECT S,L=LEAD(S,1,DATALENGTH(@L)+DATALENGTH(@D)+1)OVER(ORDER BY S)-S-DATALENGTH(@D)FROM S)
--do final select here based on start, length
SELECT ItemNumber=ROW_NUMBER()OVER(ORDER BY S),Item=SUBSTRING(@L,S,L)FROM L
;
April 14, 2014 at 12:25 pm
Miller (4/14/2014)
How about this? I'd be curious to see results of performance testing with this.
Hi....seems interesting...but really haven't the time right now to test it for you....any reason why you cannot test yourself?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 14, 2014 at 12:35 pm
Miller (4/14/2014)
OK, I ran the splitter tests.I had to modify the BetterSplit a little: Switched to varchar(8000) instead of nvarchar(max) for list parameter. This needed to get good comparison between other algorithms using these datatypes. Nvarchar(max) is much slower but how else can you get more than 8000 characters?
Also added ItemNumber to result, even though in my usage the ItemNumber is not very useful.
Also I increased the first Cte to 10 items and eliminated the 4th Cte. This results in fewer nested loops.
I didn't bother taking out the logic to handle multi-character delimiters (it might be faster without this)
Anyway, after all this I ended up with BetterSplit being about 11% faster than the DelimitedSplit8K.
Can you post the actual testing results and the testing you did? It would be great to see the whole thing put together.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2014 at 7:26 pm
Miller (4/14/2014)
Anyway, after all this I ended up with BetterSplit being about 11% faster than the DelimitedSplit8K.
I'm not sure how you came up with that number. Please explain from the results you sent me.
Also and to be sure, the DelimitedSplit8K in the test is (IIRC), the original repair from the article. A couple of folks in the discussion made some improvements on that and the current version of DelimitedSplit8K includes their enhancements (the DelimitedSplit8K in the test results isn't the latest... it's one of the others but I don't recall which one... I'll dig that info up. Might even be in the comments of the function itself (should be)).
I do thank you for doing the test. Much appreciated.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2014 at 8:22 pm
I got the results of 11% by comparing the total time to run all the tests.
Note. Some tests had bigger differences than others, and some even had the Delimited8K faster. Also, repeated executions of the test produced somewhat different results. 14% or 9% etc.
Also, when I started, I wanted a split that would work with larger strings in the range of 50000 rows which demands the varchar max. I also wanted multi-character delimiters. In this range, the BetterSplit is more than 2x faster than the Delimited8K adapted to support varchar max. That's why I thought it would be faster in the < 8000 range as well.
here is the latest function with all support for multi-byte delimiters removed.
IF OBJECT_ID('dbo.[BetterSplit]') IS NOT NULL
DROP FUNCTION dbo.BetterSplit;
GO
create FUNCTION [dbo].[BetterSplit]
(
@L VARCHAR(8000)
,@D CHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
--A,B,C used to generate large number of rows for tally
A AS(SELECT N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))A(N))
,B(N)AS(SELECT 1FROM A,A B)
,C(N)AS(SELECT 1FROM B A,B)
--Tally table
,T(N)AS(SELECT TOP(ISNULL(DATALENGTH(@L),0))ROW_NUMBER()OVER(ORDER BY(SELECT 1))FROM C)
--Start of each delimiter
,S AS(SELECT S=1UNION ALL SELECT S=N+1FROM T WHERE SUBSTRING(@L,N,1)=@D)
--Start,Length of each value in list.
--Use LEAD() to get next row start, then subtract current row start and adjust for size of delimiters
,L AS(SELECT S,L=LEAD(S,1,DATALENGTH(@L)+2)OVER(ORDER BY S)-S-1FROM S)
--do final select here based on start,length
SELECT ItemNumber=ROW_NUMBER()OVER(ORDER BY S),Item=SUBSTRING(@L,S,L)FROM L
;
Also attached is the latest test results showing that for the larger tests the BetterSplit is faster, but for some of the smaller tests, the DelimitedSplit8K is faster.
RowNumNumberOfRowsNumberOfElementsMinElementLengthMaxElementLengthDelimitedSplit8KBetterSplitMinLengthAvgLengthMaxLength%dif
6100011100.010.006151040.00%
13100021100.0160.0133122118.75%
20100041100.0230.03692442-56.52%
27100081100.0460.056265178-21.74%
341000161100.0760.1168102141-44.74%
411000321100.1530.19158206269-24.18%
481000641100.2660.38339415484-42.86%
5510001281100.5360.723716832928-34.89%
6210002561100.520.30316191619161941.73%
6910005121100.9160.6133003300330033.41%
76100011501102.0961.3773657365736534.64%
831000110200.0060.01101520-66.67%
901000210200.0160.0162131410.00%
971000410200.040.043466281-7.50%
1041000810200.070.076102126150-8.57%
11110001610200.1330.143220254300-7.52%
11810003210200.2630.293450510571-11.41%
12510006410200.5260.5693510231116-6.46%
132100012810201.0431.156194320462156-10.83%
139100025610200.5930.34341074107410742.16%
146100048010201.0530.59376747674767443.68%
1531000120300.010.012025300.00%
1601000220300.0230.0241516113.04%
1671000420300.0530.053861021200.00%
1741000820300.10.103179207237-3.00%
18110001620300.1960.193784144533.06%
18810003220300.390.3837738308801.79%
19510006420300.7660.7431586166217383.00%
202100012820301.5331.5333234332734320.00%
209100025620300.6730.32666506650665051.56%
216100029020300.7230.3674907490749050.21%
2231000130400.010.013035400.00%
2301000230400.0230.0236171810.00%
2371000430400.0730.06312414216113.70%
2441000830400.130.122592863127.69%
25110001630400.2560.245255756216.25%
25810003230400.5130.4861095115012165.26%
26510006430401.020.9262224230223859.22%
272100012830402.0361.8634490460847338.50%
279100021030400.630.28375537553755355.08%
2861000140500.010.014045500.00%
2931000240500.0230.02819110113.04%
3001000440500.0960.08616618220010.42%
3071000840500.1660.153423663929.64%
31410001640500.3230.2936967357759.29%
32110003240500.630.55314101470152612.22%
32810006440501.2861.09628592943301514.77%
335100012840502.5732.15357805886599416.32%
342100016540503.1432.7774657589774311.87%
25.80921.89615.16%
April 14, 2014 at 9:00 pm
Thanks. I'll check it out.
BTW... you know you can attach spreadsheets to a post, right?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2014 at 9:12 pm
At the risk of making a predictable contribution at this point, based on Miller's stated requirements for long Unicode strings and multi-character delimiters:
CREATE ASSEMBLY [MultiSplit]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300E3DEE24F0000000000000000E00002210B010800000E00000006000000000000FE2C0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000A42C000057000000004000009003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000040D000000200000000E000000020000000000000000000000000000200000602E7273726300000090030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000E02C000000000000480000000200050054220000500A00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D2026F1000000A2D1A036F1000000A2D12026F1100000A036F1100000A73050000062A168D15000001168D1500000173050000062A000000133002002A000000010000110274030000020A03067B01000004281200000A810400000104067B02000004281300000A81050000012A1E02281400000A2A5602281400000A02037D0100000402047D020000042A03300300530000000000000002281400000A02037D0600000402038E697D0800000402047D0700000402048E69D27D0900000402027B0900000417FE017D0A00000402167D0500000402167D0300000402027B09000004155A7D040000042AAA027B05000004027B06000004027B03000004027B04000004027B0300000459731500000A73040000062A000013300300C90000000200001102257B0500000417587D05000004027B04000004027B080000043202162A02027B04000004027B09000004587D03000004027B030000040A2B65170B027B0A0000042C16027B060000040693027B0700000416932E39160B2B35160C2B28060858027B080000042F14027B0600000406085893027B0700000408932E04160B2B0E081758D20C08027B0900000432CF072C0902067D04000004172A0617580A06027B08000004329202027B04000004027B09000004587D0300000402027B080000047D04000004172A5A02167D0300000402027B09000004155A7D040000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000002C030000237E000098030000A803000023537472696E6773000000004007000008000000235553004807000010000000234755494400000058070000F802000023426C6F6200000000000000020000015717A2010902000000FA2533001600000100000016000000040000000A000000080000000900000001000000150000000D0000000200000001000000010000000100000001000000020000000200000000000A0001000000000006005C0055000600760063000A00A3008E000A00BE008E000A00C7008E000A00840169010600C301A4010600FA01E80106001102E80106002E02E80106004D02E80106006602E80106007F02E80106009A02E8010600B502E8010600CE02A4010600E202E80106001B03FB0206003B03FB020A006403690106008E03550006009F03550000000000010000000000010001000100100019000000050001000100030110002E000000050001000400020110003B0000000500030005002300E50022002300EE00250001000E0122000100160122000100E500220021001E013E00210028013E0021003201220021003C0142002100490145005020000000009600AC000A0001008820000000009600D10013000300BE20000000008618DF001E000600C620000000008618DF0028000600DC20000000008618DF002E0008003B2100000000E609F30036000A00682100000000E601FF003A000A003D2200000000E60108011E000A0000000100630100000200960100000100A00102000200E50002000300EE0000000100D00100000200D90100000100DE01000002009601040009003100DF001E003900DF001E004100DF00B0004900DF00B0005100DF00B0005900DF00B0006100DF00B0006900DF00B0007100DF00B0007900DF00B0008100DF00B5008900DF00B0009100DF00BA009900DF001E00A100DF001E00190079033A00190084036302210093036802290093036E020900DF001E00B100DF00790220007B00BF0024000B004C002E0033009D022E001B0087022E00230097022E002B0097022E004B0097022E007300D6022E004300AC022E003B0087022E005B0097022E006B00CD0244000B007E00740281020400010000005B0148000200060003000480000001000000CB11D9980000000000005903000002000000000000000000000001004C0000000000020000000000000000000000010082000000000003000200040002000000003C4D6F64756C653E004D756C746953706C69742E646C6C0055736572446566696E656446756E6374696F6E73004F75747075745265636F72640053706C6974537472696E674D756C7469006D73636F726C69620053797374656D004F626A6563740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C6974537472696E675F4D756C74690053716C496E7433320053716C537472696E670046696C6C526F775F4D756C7469002E63746F720073657175656E6365006974656D006765745F43757272656E74004D6F76654E657874005265736574006C617374506F73006E657874506F7300746865537472696E670064656C696D6974657200737472696E674C656E0064656C696D697465724C656E00697353696E676C654368617244656C696D0043757272656E7400496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650053657175656E6365004974656D00546865537472696E670053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D756C746953706C69740053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172006F705F496D706C6963697400537472696E670000000003200000000000A3AEF14706753B429B464E1C5D2862700008B77A5C561934E0890800021209120D120D0A0003011C1011111011150320000102060802060E05200201080E072002011D031D030320001C0320000203061D030206050206020328001C31010003005408074D617853697A65FFFFFFFF54020D497346697865644C656E6774680054020A49734E756C6C61626C650031010003005408074D617853697A65FF00000054020D497346697865644C656E6774680054020A49734E756C6C61626C6500042001010E0420010102042001010881A201000600540E1146696C6C526F774D6574686F644E616D650D46696C6C526F775F4D756C74695455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E0F5461626C65446566696E6974696F6E274974656D4E756D62657220696E74656765722C204974656D206E766172636861722834303030290420001D0305000111110805000111150E040701120C072003011D0308080507030802050F01000A4D756C746953706C697400000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313200000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000CC2C00000000000000000000EE2C0000002000000000000000000000000000000000000000000000E02C00000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100D998CB1100000100D998CB113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F0066007400000040000B000100460069006C0065004400650073006300720069007000740069006F006E00000000004D0075006C0074006900530070006C00690074000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003500350035002E00330039003100320039000000000040000F00010049006E007400650072006E0061006C004E0061006D00650000004D0075006C0074006900530070006C00690074002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F0066007400200032003000310032000000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004D0075006C0074006900530070006C00690074002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D006500000000004D0075006C0074006900530070006C00690074000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003500350035002E00330039003100320039000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003500350035002E00330039003100320039000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000003D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[SplitString_Multi]
(
@Input nvarchar(max),
@Delimiter nvarchar(255)
)
RETURNS TABLE
(
[ItemNumber] integer NULL,
[Item] nvarchar(4000) NULL
)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [MultiSplit].[UserDefinedFunctions].[SplitString_Multi];
GO
SELECT
Split.ItemNumber,
Split.Item
FROM dbo.SplitString_Multi(N'SQLxxCLRxxISxxCOOL', N'xx') AS Split;
Output:
ItemNumberItem
1SQL
2CLR
3IS
4COOL
Source code:
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[SqlFunction
(
FillRowMethodName = "FillRow_Multi",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true,
TableDefinition = "ItemNumber integer, Item nvarchar(4000)"
)
]
public static IEnumerator SplitString_Multi
(
[SqlFacet(MaxSize = -1, IsFixedLength = false, IsNullable = false)]
SqlChars Input,
[SqlFacet(MaxSize = 255, IsFixedLength = false, IsNullable = false)]
SqlChars Delimiter
)
{
return
(
(Input.IsNull || Delimiter.IsNull) ?
new SplitStringMulti(new char[0], new char[0]) :
new SplitStringMulti(Input.Value, Delimiter.Value));
}
private sealed class OutputRecord
{
internal readonly int sequence;
internal readonly string item;
public OutputRecord(int Sequence, string Item)
{
this.sequence = Sequence;
this.item = Item;
}
}
public static void FillRow_Multi(object obj, out SqlInt32 sequence, out SqlString item)
{
OutputRecord r = (OutputRecord)obj;
sequence = r.sequence;
item = r.item;
}
public sealed class SplitStringMulti : IEnumerator
{
public SplitStringMulti(char[] TheString, char[] Delimiter)
{
theString = TheString;
stringLen = TheString.Length;
delimiter = Delimiter;
delimiterLen = (byte)(Delimiter.Length);
isSingleCharDelim = (delimiterLen == 1);
sequence = 0;
lastPos = 0;
nextPos = delimiterLen * -1;
}
#region IEnumerator Members
public object Current
{
get
{
return new OutputRecord(sequence, new string(theString, lastPos, nextPos - lastPos));
}
}
public bool MoveNext()
{
sequence++;
if (nextPos >= stringLen)
return false;
else
{
lastPos = nextPos + delimiterLen;
for (int i = lastPos; i < stringLen; i++)
{
bool matches = true;
if (isSingleCharDelim)
{
if (theString != delimiter[0])
matches = false;
}
else
{
for (byte j = 0; j < delimiterLen; j++)
{
if (((i + j) >= stringLen) || (theString != delimiter[j]))
{
matches = false;
break;
}
}
}
if (matches)
{
nextPos = i;
return true;
}
}
lastPos = nextPos + delimiterLen;
nextPos = stringLen;
return true;
}
}
public void Reset()
{
lastPos = 0;
nextPos = delimiterLen * -1;
}
#endregion
private int lastPos;
private int nextPos;
private int sequence;
private readonly char[] theString;
private readonly char[] delimiter;
private readonly int stringLen;
private readonly byte delimiterLen;
private readonly bool isSingleCharDelim;
}
};
April 14, 2014 at 10:32 pm
I agree. A CLR would be the best way and you made it easy enough for everyone to use. Thanks. The "Split" function in the original article was much faster than any of the T-SQL methods.
The only thing I don't like about the one that Adam posted is that it ignores leading/trailing and consecutive delimiters. For the kind of work I do, those are important especially for "ragged right" splits.
The link to Adam's article is broken. Here it is again...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 571 through 585 (of 990 total)
You must be logged in to reply to this topic. Login to reply