x

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.

more ▼

asked Oct 07, 2009 at 06:38 AM in Default

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

:)

more ▼

answered Oct 07, 2009 at 07:52 AM

robvolk gravatar image

robvolk ♦
129 1 1 3

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 07, 2009 at 03:42 PM

Jeff Smith gravatar image

Jeff Smith ♦
56 1

write a UDF that performs the split and returns a TABLE with the results. Now, that has been around since SQL 2000,

Indeed. I have separate UDFs that return INT or VARCHAR resultsets so that the subsequent JOIN is datatype-matched
Oct 07, 2009 at 05:14 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 07, 2009 at 03:21 PM

mrdenny gravatar image

mrdenny
908 3

How would you "pass the broken pieces to the database"? Lets say I want to match a product code against any in the list "ABC123,DEF456,GHI789" (currently I would pass that string to my Sproc and SPLIT it to a temporary table there, and then use that temporary table in a JOIN)
Oct 07, 2009 at 03:26 PM Kristen ♦
In SQL 2008 you could use a table input parameter and pass the list into the procedure as a recordset then use a simple subquery lookup WHERE ProductCode IN (SELECT ProductCode FROM YourInputParameter). Or pass in a XML document with multiple values. Takes some extra CPU power, but you don't have to do any funky text manipulation in the proc.
Oct 07, 2009 at 04:09 PM mrdenny
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x35
x22

asked: Oct 07, 2009 at 06:38 AM

Seen: 2288 times

Last Updated: Oct 07, 2009 at 06:38 AM