June 18, 2010 at 1:14 pm
Comments posted to this topic are about the item Parsing / breaking character separated values in a table in one query
July 14, 2010 at 2:38 am
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.
;with spc (id, num) as
(selecta.id, 0
from @vTable a
union all
select a.id, t.num
from @vTable a
inner join tally t
on 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 Val
from @vTable v
inner join spc a on a.id = v.id
left join spc b on b.id = a.id and b.num > a.num
and not exists (
select 1
from spc c
where c.id = b.id and c.num > a.num and c.num < b.num
)
order by a.id, a.num
July 14, 2010 at 5:47 am
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;
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, 0
from @vTable a
union all
select a.id, t.num
from @vTable a
inner join tally t
on 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 Val
from @vTable v
inner join spc a on a.id = v.id
left join spc b on b.id = a.id and b.num > a.num
and not exists (
select 1
from spc c
where c.id = b.id and c.num > a.num and c.num < b.num
)
order by a.id, a.num
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...
;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 @vTable
Union all
Select 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.ID
and wcte.strpos <> 1
)Select id,Val from wcte
order by id
OPTION (MAXRECURSION 500 )
The difference is very clear.
Thanks once again and please update me if am going the wrong way....
July 14, 2010 at 6:39 am
You're right, my suggestion doesn't perform well.
It could be done better - here is a slightly improved version
;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
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!
July 14, 2010 at 9:45 pm
:-)its best
July 19, 2010 at 1:33 pm
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 display
Atif
Asif
How should we handle this?
TIA,
Doug
July 19, 2010 at 1:35 pm
Atif,
That was a nice hint on the (MAXRECURSION 500 ) - I didn't know that!
Best,
Doug
July 19, 2010 at 1:42 pm
Atif,
That was because you used
substring(val,0,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val
instead of
substring(val,1,case when charindex(' ',val,0) = 0 then Len(val) else charindex(' ',val,0) + 1 end) as val
Cheers,
Doug
July 19, 2010 at 10:17 pm
Thanks for pointing Doug. Try this;
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 @vTable
Union all
Select 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.ID
and wcte.strpos <> 1
)Select id,Val from wcte
where Val <> ''
order by id
OPTION (MAXRECURSION 500 )
I have replaced Len(Val) with Len(Val) + 1 to get the required outout.
July 20, 2010 at 6:27 am
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
July 20, 2010 at 11:36 am
plz check my last posted query. Its working fine.
July 23, 2010 at 9:42 am
There is some significant overlap between this post 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:
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)
Your code gives timings on my box of:
SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 564 ms.
Following ColdCoffee's suggestion from the above link, and putting Jeff's tally table method into a function:
CREATE FUNCTION dbo.fn_Split_TALLY
(@delimited nvarchar(max)
,@delimiter nchar(1)
)
RETURNS TABLE
as
RETURN
SELECT SUBSTRING(@delimiter+@delimited +@delimiter,N+1,CHARINDEX(@delimiter,@delimiter+@delimited+@delimiter,N+1)-N-1) as Val
FROM(
SELECT TOP (LEN(@delimited)+1) ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROMMaster.sys.All_Columns t1
CROSS JOINMaster.sys.All_Columns t2
) t
WHERE SUBSTRING(@delimiter+@delimited +@delimiter,N,1) = @delimiter --Notice how we find the comma
GO
and calling it like this against your sample table:
selectv.id, s.val
from@vTable v
cross apply dbo.fn_Split_TALLY(v.val,' ') s
gives timings of:
SQL Server Execution Times:
CPU time = 7937 ms, elapsed time = 7931 ms.
Slightly surprised by that performance, so I rewrote the script to use an existing indexed TALLY table, and the timings change to:
SQL Server Execution Times:
CPU time = 4719 ms, elapsed time = 4716 ms.
I also found some iterative looping code (just substrings in a WHILE loop):
CREATE FUNCTION fn_Split_LOOP
( @Stringnvarchar(max),--String to be parsed
@Delimiternchar(1)--Delimiter to use to parse the string
)
RETURNS @RtnTable table(
idint,
Valnvarchar(max))
AS
begin
Declare @Itemnvarchar(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 @ItemNoint--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
return
end
GO
which is called via cross apply same as above, and this gives timings of:
SQL Server Execution Times:
CPU time = 344 ms, elapsed time = 385 ms.
I also found this link, suggested by Eralper in the link above, and created a function to split the strings using XML:
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)
)
AS
BEGIN
declare @xml xml set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
selectr.value('.','varchar(max)') as item
from@xml.nodes('//root/r') as records(r)
RETURN
END
GO
which gives the fastest timings of:
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 331 ms.
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.
July 23, 2010 at 1:08 pm
Interesting and superb testing. I would definitely check the XML approach on monday morning. Its weekend now.:-)
Thanks.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply