The databases I look after are still back in the stone age on SQL 2000, but I'm curious to know how Splitting a delimited string has improved since SQL2000.
asked
How has Splitting a delimited string improved since SQL2000?
The SQL 2008 (and I think 2005) samples contain code for CLR functions that can split and concatenate strings. (the concatenate function is an aggregate, and it's very handy). I can't find a URL but it should be in BOL under "Samples".
There's also my favorite:
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows
:)
Other than SQLCLR there's no really efficient way to handle this is SQL Server 2005+. It is still best to handle the string breaking at the client side and pass the broken pieces to the database.
I have found the best way is to simply write a UDF that performs the split and returns a TABLE with the results. Now, that has been around since SQL 2000, so no difference there. However, since 2005, we can now use CROSS APPLY (and OUTER APPLY), meaning we can "join" to the multi-row results returned from these UDF's to quickly take a table of CSV strings and return them all broken out into individual rows. That, to me, is still the quickest, shortest and most efficient way to handle things.
In other words, something like this:
select t.id, x.value
from t
cross apply dbo.Split(t.CSV_Column) x
