0

So, I am passing 2 arrays into a stored procedure in Sql Server 2005 as comma delimited lists. These arrays are synchrinized by their order, i.e. A1(0) corresponds to A2(0), A1(1) corresponds to A2(1), ... and so on.

How would I parse these arrays into 2 columns in a temp table or table variable?

EDIT: Ok, so I am passing 2 comma delimited strings into a stored procedure. I want to get these into a table with 2 columns. For instance, if

@param1 = 'field1, field2, field3'
@param2 = 'value1, value2, value3'

I want to get a table like

 |----------------------------------|
 |  Fields         | Values         |
 |----------------------------------|
 |  field1         | value1         |
 |  field2         | value2         |
 |  field3         | value3         |
 |----------------------------------|

I do not know how many fiel/value pairs there may be, but in use, there really should not be more than 10 or 15.

Also, I do not necessarily need to maintain a particular order, just as long as the field/value pairs are maintained.

WE have a function that will parse one comma delimited list - could i simply copy that and create one that handles 2 strings, or parse each list separately and then join them,....

estump
  • 1
  • 1
  • There's a really excellent table-valued function by Jeff Moden from sqlservercentral called DelimitedSplit8K that does exactly this (converts a delimited string into a table). You might find it useful. http://www.sqlservercentral.com/articles/Tally+Table/72993/ – Aushin Apr 29 '13 at 14:32
  • @Aushin are you sure Jeff's splitter maintains order? That is the key part of this question. – Aaron Bertrand Apr 29 '13 at 15:00
  • @frikozoid it's CSV. `1,2,3` and `a,b,c`. – Aaron Bertrand Apr 29 '13 at 15:01
  • Here is a function that maintains the order of the incoming list. http://stackoverflow.com/a/10908469/61305 – Aaron Bertrand Apr 29 '13 at 15:02
  • @AaronBertrand I believe it does. It would make the 'array' go from 0-based to 1-based though. And if that's not actually true, I know that adding ORDER BY ItemNumber to the end of the final select statement would achieve that. All that said, I did miss that detail in his question, thanks for pointing it out. – Aushin Apr 29 '13 at 15:05
  • @Aushin also you might be interested in this (even though it did flare up a bunch of flames) : http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings, http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up and http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql (though those don't handle order, they're easy to extend) – Aaron Bertrand Apr 29 '13 at 15:20
  • @AaronBertrand Thanks I'll read them over! – Aushin Apr 29 '13 at 15:35
  • Yes, just use a single function that returns an ordering column, pass each list to it, and join the output on the ordering column. – Aaron Bertrand Apr 29 '13 at 15:48
  • Yes, it maintains the correct order. – Jeff Moden Jul 02 '13 at 02:14
  • @Aaron - The first link you provided is one of the older split functions that uses concatenation and and will slow down much slower than even a While Loop splitter on larger strings. As to the "flames", thanks for the reminder... I'll have to revisit that thread to see what else you did wrong with the splitter. – Jeff Moden Jul 02 '13 at 02:19
  • 6
    @jeff would you stop being so disrespectful? All I did to your beloved splitter was make it handle more than 8K and show alternative, more efficient ways to do so. Have you done anything about that yet? – Aaron Bertrand Jul 02 '13 at 10:55
  • Not being disrespectful, Aaron. You used it incorrectly and said it wasn't working correctly. The splitter works fine so there was nothing for me to do there. When I have some time, I'll return to that blog entry and try to explain and, yes, I'll do it respectfully. ;-) – Jeff Moden Jul 05 '13 at 19:50

0 Answers0