SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Atif Sheikh / Parsing / breaking character separated values in a table in one query / Latest Posts

RE: Parsing / breaking character separated values in a table in one query

Atif-ullah Sheikh — Fri, 23 Jul 2010 13:08:34 GMT

Interesting and superb testing. I would definitely check the XML approach on monday morning. Its weekend now.:-)Thanks.

RE: Parsing / breaking character separated values in a table in one query

paul_ramster — Fri, 23 Jul 2010 09:42:15 GMT

There is some significant overlap between [b][url=http://www.sqlservercentral.com/Forums/Topic955429-392-1.aspx]this post[/url][/b] and yours, so I did some performance testing.On the combination of both the test data you supply, and the 8000 replications of "123456" that Jeff uses in his article:[code="sql"]Declare @vTable Table(id int,val varchar(Max))Insert into @vTable Values (1,'Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd')Insert into @vTable Values (2,'Asif Sheikh1 Sheikh2 Sheikh3') Insert into @vTable select 3, LEFT(REPLICATE(CAST('123456 ' AS VARCHAR(MAX)),8000),(8000*7)-1)[/code]Your code gives timings on my box of:[code="other"] SQL Server Execution Times: CPU time = 375 ms, elapsed time = 564 ms.[/code]Following ColdCoffee's suggestion from the above link, and putting Jeff's tally table method into a function:[code="sql"]CREATE FUNCTION dbo.fn_Split_TALLY ( @delimited nvarchar(max) , @delimiter nchar(1) ) RETURNS TABLEasRETURNSELECT SUBSTRING(@delimiter+@delimited +@delimiter,N+1,CHARINDEX(@delimiter,@delimiter+@delimited+@delimiter,N+1)-N-1) as ValFROM ( SELECT TOP (LEN(@delimited)+1) ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N FROM Master.sys.All_Columns t1 CROSS JOIN Master.sys.All_Columns t2 ) t WHERE SUBSTRING(@delimiter+@delimited +@delimiter,N,1) = @delimiter --Notice how we find the commaGO[/code]and calling it like this against your sample table:[code="sql"]select v.id, s.valfrom @vTable vcross apply dbo.fn_Split_TALLY(v.val,' ') s[/code]gives timings of:[code="other"] SQL Server Execution Times: CPU time = 7937 ms, elapsed time = 7931 ms.[/code]Slightly surprised by that performance, so I rewrote the script to use an existing indexed TALLY table, and the timings change to:[code="other"] SQL Server Execution Times: CPU time = 4719 ms, elapsed time = 4716 ms.[/code]I also found some iterative looping code (just substrings in a WHILE loop):[code="sql"]CREATE FUNCTION fn_Split_LOOP( @String nvarchar(max), --String to be parsed @Delimiter nchar(1) --Delimiter to use to parse the string)RETURNS @RtnTable table ( id int, Val nvarchar(max))ASbegin Declare @Item nvarchar(max) --Holds indivialual items parsed --from the string Declare @StartPos int --Start position of the next item in the string Declare @EndPos int --End position of the next item in the string Declare @Pos int --Position of the next delimiter Declare @LenDelimiter int --Lengh of the delimiter Declare @LastItem bit --Indeicates whether this is the last item in --the string Declare @LenString int --Length of the string to be parsed Declare @ItemNo int --Item number --Initialize variables Set @StartPos = 1 Set @LastItem = 0 Set @LenString = Len(@String) Set @LenDelimiter = 1--Len(@Delimiter) -- len() doesn't work if the delimiter is a space! set @ItemNo = 1 --The following loop parses through the @String parameter to extract --the items. It inserts one row in the RtnTable table for each item. while @LastItem = 0 begin --Get the position of the next delimiter Set @Pos = CHARINDEX(@Delimiter, @String, @StartPos) --If @Pos is 0, then we have reached the last item in the string, so --process the last item, then exit the loop. if @Pos > 0 Set @EndPos = @Pos else begin Set @EndPos= @LenString + 1 Set @LastItem = 1 end --Extract the item from the string and trim any leading or trailing blanks. Set @Item = Rtrim(LTrim(Substring(@String, @StartPos, @EndPos-@StartPos))) --Insert the item into the return table insert @RtnTable values(@ItemNo, @Item) --If this was the last item, then exit the loop, else --increment the @StartPos and @ItemNo and go through again. if @LastItem = 1 break else begin set @ItemNo = @ItemNo + 1 Set @StartPos = @EndPos + @LenDelimiter end end returnendGO[/code]which is called via cross apply same as above, and this gives timings of:[code="other"] SQL Server Execution Times: CPU time = 344 ms, elapsed time = 385 ms.[/code]I also found [b][url=http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx]this link[/url][/b], suggested by Eralper in the link above, and created a function to split the strings using XML:[code="sql"]CREATE FUNCTION dbo.fn_Split_XML ( @delimited nvarchar(max) , @delimiter nchar(1) ) RETURNS @t TABLE ( id int identity(1,1)-- Id column can be commented out, not required for sql splitting string , val nvarchar(max) )ASBEGIN declare @xml xml set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>' insert into @t(val) select r.value('.','varchar(max)') as item from @xml.nodes('//root/r') as records(r) RETURNENDGO[/code]which gives the fastest timings of:[code="other"] SQL Server Execution Times: CPU time = 265 ms, elapsed time = 331 ms.[/code]This suggests that this is not a good place to use a TALLY table, that recursive CTE gives reasonable performance, WHILE loops have their place, but XML is fastest.Which isn't necessarily what I expected.

RE: Parsing / breaking character separated values in a table in one query

Atif-ullah Sheikh — Tue, 20 Jul 2010 11:36:51 GMT

plz check my last posted query. Its working fine.

RE: Parsing / breaking character separated values in a table in one query

Douglas Osborne-456728 — Tue, 20 Jul 2010 06:27:11 GMT

Atif,My point was SUBSTRING is 1 based, not 0 based.DECLARE @String VARCHAR(50)SET @String = 'Doug'PRINT SUBSTRING( @String, 1, 3 )PRINT SUBSTRING( @String, 0, 3 )It won't error out - but it will not work correctly either.Doug

RE: Parsing / breaking character separated values in a table in one query

Atif-ullah Sheikh — Mon, 19 Jul 2010 22:17:55 GMT

Thanks for pointing Doug. Try this;[code="sql"]Declare @vTable Table(id int,val varchar(Max))Insert into @vTable Values (1,'Atif1 SS')Insert into @vTable Values (2,'Asif2 SS') ;with wcte (id,val,strpos) as(Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val)+1 else charindex(' ',val,0) + 1 end) as val,charindex(' ',val,0) + 1 strpos from @vTableUnion allSelect b.id,substring(b.val,strpos,case when charindex(' ',b.val,strpos) = 0 then Len(b.val)+1 else charindex(' ',b.val,strpos) - strpos end) as val,charindex(' ',b.val,strpos) + 1 strpos from @vTable b Inner Join wcte on wcte.id = b.IDand wcte.strpos <> 1 )Select id,Val from wcte where Val <> ''order by idOPTION (MAXRECURSION 500 )[/code]I have replaced Len(Val) with Len(Val) + 1 to get the required outout.

RE: Parsing / breaking character separated values in a table in one query

Douglas Osborne-456728 — Mon, 19 Jul 2010 13:42:32 GMT

Atif,That was because you usedsubstring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as valinstead ofsubstring(val,1,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as valCheers,Doug

RE: Parsing / breaking character separated values in a table in one query

Douglas Osborne-456728 — Mon, 19 Jul 2010 13:35:45 GMT

Atif,That was a nice hint on the (MAXRECURSION 500 ) - I didn't know that!Best,Doug

RE: Parsing / breaking character separated values in a table in one query

Douglas Osborne-456728 — Mon, 19 Jul 2010 13:33:12 GMT

Atif,It looks like any time you hit a line which has no spaces eg Insert into @vTable Values (1,'Atif1')Insert into @vTable Values (2,'Asif2') You will displayAtifAsifHow should we handle this?TIA,Doug

RE: Parsing / breaking character separated values in a table in one query

n08022 — Wed, 14 Jul 2010 21:45:06 GMT

:-)its best

RE: Parsing / breaking character separated values in a table in one query

paul_ramster — Wed, 14 Jul 2010 06:39:05 GMT

You're right, my suggestion doesn't perform well.It could be done better - here is a slightly improved version[code="sql"];with spc (id, val, num, k) as (select a.id, a.val, t.num, ROW_NUMBER() over (partition by a.id order by t.num) as k from @vTable a inner join tally t on t.num <= len(a.val)+2 and SUBSTRING(' '+a.val+' ',t.num,1) = ' ')select a.id, substring(a.Val, a.num, b.num- 1 - a.num) from spc a inner join spc b on b.id = a.id and b.k = a.k+1 [/code]My aversion to recursive CTEs may well be unjustified, it's just that you do need to understand and allow for the likely recursion depth when developing them.However, you also need to be careful when writing any SQL, as the poor performance of my iterative code demonstrates excellently!

RE: Parsing / breaking character separated values in a table in one query

Atif-ullah Sheikh — Wed, 14 Jul 2010 05:47:42 GMT

Dear paul_ramster,First of all, thanks for the new version of the query and sharing your knowledge. That is why we (at least I visit this website.) The point raised by you is valid. But it can be handeled by MAXRECURSION query hint. I should have mentioned it in the script. Thanks for pointing it out.The query provided by you does not require the MAXRECURSION hint. Tally table solution was in my mind when i attempted this approach. But, hard to believe, tally table solution was a bit slower. Even your query is vary slow with 424 items in a list. here is the code;[code="sql"]Declare @vTable Table(id int,val varchar(Max))Insert into @vTable Values (1,'Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd')Insert into @vTable Values (2,'Asif Sheikh1 Sheikh2 Sheikh3') ;with spc (id, num) as(select a.id, 0from @vTable aunion allselect a.id, t.numfrom @vTable ainner join tally ton t.num <= len(a.val) and SUBSTRING(a.val,t.num,1) = ' ')select a.id, substring(v.val, a.num + 1, coalesce(b.num-1, len(v.val)) - a.num) as Valfrom @vTable vinner join spc a on a.id = v.idleft join spc b on b.id = a.id and b.num > a.numand not exists (select 1from spc cwhere c.id = b.id and c.num > a.num and c.num < b.num)order by a.id, a.num[/code]Goes worst if clustered index is created on NUM column if tally table. (It did atleast on my PC with)Here is my query with MAXRECURSION query hint...[code="sql"];with wcte (id,val,strpos) as(Select id,substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val,charindex(' ',val,0) + 1 strpos from @vTableUnion allSelect b.id,substring(b.val,strpos,case when charindex(' ',b.val,strpos) = 0 then Len(b.val) else charindex(' ',b.val,strpos) - strpos end) as val,charindex(' ',b.val,strpos) + 1 strpos from @vTable b Inner Join wcte on wcte.id = b.IDand wcte.strpos <> 1 )Select id,Val from wcte order by idOPTION (MAXRECURSION 500 )[/code]The difference is very clear. Thanks once again and please update me if am going the wrong way....

RE: Parsing / breaking character separated values in a table in one query

paul_ramster — Wed, 14 Jul 2010 02:38:11 GMT

Nice. Can't help thinking, though, that recursive CTEs are a solution looking for a problem.That method works well for short strings, but longer strings will soon hit the maximum recursion limit, and raising the recursion level means that the query can start to eat memory.If you have a "free" tally table, then something iterative like this provides similar performance without the limitations of using recursion.[code="sql"];with spc (id, num) as(select a.id, 0from @vTable aunion allselect a.id, t.numfrom @vTable ainner join tally ton t.num <= len(a.val) and SUBSTRING(a.val,t.num,1) = ' ')select a.id, substring(v.val, a.num + 1, coalesce(b.num-1, len(v.val)) - a.num) as Valfrom @vTable vinner join spc a on a.id = v.idleft join spc b on b.id = a.id and b.num > a.numand not exists (select 1from spc cwhere c.id = b.id and c.num > a.num and c.num < b.num)order by a.id, a.num[/code]

Parsing / breaking character separated values in a table in one query

Atif-ullah Sheikh — Fri, 18 Jun 2010 13:14:48 GMT

Comments posted to this topic are about the item [B]Parsing / breaking character separated values in a table in one query [/B]