function to Convert multiple comma delimited record into columns

Question

This is a sample of my data table:

   Record
    9142,CSVSO62598,80413,2013-12-22 00:00:00.000,WB-H098,1,4.99,Individual,Sales extract OK!,255.0
    9143,CSVSO53548,49098,2013-07-31 00:00:00.000,BK-R89B-48,4,5864.04,Individual,Sales extract OK!,255.0
    9144,CSVSO70506,107403,2014-04-12 00:00:00.000,BC-M005,1,9.99,Individual,Sales extract OK!,255.0

I would like to create a function to put each values in is respective columns: This is the table:

CREATE TABLE #Temp(ID int
,SalesOrderNumber varchar(15)
,SalesOrderDetailID int
,OrderDate date -- varchar(25)
,ProductNumber varchar(25)
,Quantity int
,LineTotal money
,CustomerType varchar(25)
,TestData_1 varchar(50)
,TestData_2 varchar(50))

and i have 12134 record

is it for MS Sql Server? What version? (it really matters!) add this as tags — Paul Maxwell, Nov 15 '17 at 02:55
and that is why I asked for that information, you do realize there are at least several hundred "split string" udf's available — Paul Maxwell, Nov 15 '17 at 03:11

score 0 · Answer 1 · answered Nov 15 '17 at 03:33

If performance is an issue I suggest you commence from here: https://dba.stackexchange.com/a/21081/29210

If you can there is a CLR by Adam Machanic, if you can't do that then use a numbers table (as explained there also) and there are other well known references also on that answer. (oh, there are alternatives to numbers table if needed, using simple cross joins.)

Don't forget to be calling these functions via an apply operator, e.g. https://stackoverflow.com/a/455116/2067753

function to Convert multiple comma delimited record into columns

1 Answers1