SQL Server 2005 seperate stored procedure CSV value into multiple columns?

Question

I'm a SQL Server 2005 newb and I have to do something that should be easy but I'm having difficulties.

For the time being my stored procedure is storing csv in one column in my db. I need to break apart the csv into multiple columns. The number of values in a the CSV parameter is static, there is always gonna be 8 values and they are always going to be in the same order. Here is an example of the data:

req,3NxWZ7RYQVsC4chw3BMeIlywYqjxdF5IUX8GMUqgJlJTztcXQS,192.168.208.78,getUserInfo,AssociateService,03303925,null,M042872, 

What is the best function to use to separate this parameter in a stored proc so I can then insert it into separate columns? I was looking at substring but that seems to be positional and not regex aware??

Thanks, Chris

Answer 1

SQL Server doesn't have the native functionality; you have to build a function (generally referred to as "split"). This thread provides a number of TSQL options, looking for the same answer you're after--what performs best. Where they lack is in testing large amounts of comma delimited data, but then your requirement is only for eight values per column anyway...

SQL Server 2005+ supports SQLCLR, where functionality can be handed off to .NET code. The .NET code has to be deployed to the SQL Server instance as an assembly, and TSQL functions/procedures need to be created to expose the functionality within the assembly.

Answer 2

SUBSTRING is positional, but you can use the CHARINDEX function to find the position of each delimiter, and grab the SUBSTRINGs between each comma.

It's a clunky solution, to be sure, but the impact is minimized given a small static number of fields that always appear in order...

Answer 3

SQL Server isn't very strong in string handling - especially if you cannot use SQL-CLR to help.

You might be better off doing this beforehand, in your client application, using something like FileHelpers, a C# library which very easily imports a CSV and breaks it apart into an object for each row.

Once you have that, you can easily loop through the array of objects you have and insert those individual object properties into a relational table.