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')

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) = ','

SELECT distinct sid, sname, S.a.value('.', 'VARCHAR(100)') AS splitVal
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)


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s