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

avatar image

Kristen ♦
2.2k 7 11 14

(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

avatar image

robvolk ♦
140 1 4 4

(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

avatar image

Jeff Smith ♦
67 2 2

(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

avatar image

mrdenny
928 2 5

(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.

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:

x39
x27

asked: Oct 07, 2009 at 06:38 AM

Seen: 2729 times

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

Copyright 2016 Redgate Software. Privacy Policy