Split Inline with a Number Table Is Faster Than a Common XML Splitter.



 declare @t table (sid int, sname varchar(50), csku varchar(50))
 Insert into @t values(1,'a1','ab11,ab12,ab13'),(2,'b2','xx11,xx12,xx13,xx14')

 --Faster
select sid, sname,  substring(csku, n, charindex(',', csku + ',', n) - n)  splitVal
from @t
cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) d(n) 
Where n <= len(csku) AND substring(',' + csku, n, 1) = ','


--Slower
SELECT distinct sid, sname, S.a.value('.', 'VARCHAR(100)') AS splitVal
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(csku, ',', '</r><r>')+ '</r></H>' AS XML) AS [vals]
FROM @t) d
 
CROSS APPLY d.[vals].nodes('/H/r') S(a)


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s