question

Kristen avatar image
Kristen asked

How has Splitting a delimited string improved since SQL2000?

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.

csvsplit
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

robvolk avatar image
robvolk answered

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

:)

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mrdenny avatar image
mrdenny answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Smith avatar image
Jeff Smith answered

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            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.