Split T-SQL string into columns by delimiter?-CodePudding

I have a table with a single column using a hyphen-delimited set of eight values, some of which are blank. I am attempting to split this string into columns, with each value corresponding to the position of the delimited string:

Table1:

Record	StringValue
rec1	422100----130-1034-10901-12000
rec2	421100--CORP--130-1034--
rec3	423000----130-1561--
rec4	111500-------
rec5	661300-710-CORP--355-1106-10901-10100

Desired Result:

Record	col1	col2	col3	col4	col5	col6	col7	col8
rec1	422100	NULL	NULL	NULL	130	1034	10901	12000
rec2	421100	NULL	CORP	NULL	130	1034	NULL	NULL
rec3	423000	NULL	NULL	NULL	130	1561	NULL	NULL
rec4	111500	NULL	NULL	NULL	NULL	NULL	NULL	NULL
rec5	661300	710	CORP	NULL	355	1106	10901	10100

I have tried a complicated series of SUBSTRING/CHARINDEX functions which will work, but I am curious if there is a more appropriate solution? When I try the PARSENAME function it returns only NULL values because there is not a value for every position in the string.

SELECT 
    Record
    ,PARSENAME(REPLACE(StringValue, '-', '.'), 1) AS col1
    ,PARSENAME(REPLACE(StringValue, '-', '.'), 2) AS col2
    ,PARSENAME(REPLACE(StringValue, '-', '.'), 3) AS col3
    ,PARSENAME(REPLACE(StringValue, '-', '.'), 4) AS col4
    ,PARSENAME(REPLACE(StringValue, '-', '.'), 5) AS col5
    ,PARSENAME(REPLACE(StringValue, '-', '.'), 6) AS col6
    ,PARSENAME(REPLACE(StringValue, '-', '.'), 7) AS col7
    ,PARSENAME(REPLACE(StringValue, '-', '.'), 8) AS col8
FROM table1

CodePudding user response：

As you know parsename() is limited to 4 positions.

Here is a JSON option (assuming 2016 )

 Select A.Record
       ,Pos1 = nullif(JSON_VALUE(JS,'$[0]'),'') --nullif() optional otherwise empty string
       ,Pos2 = nullif(JSON_VALUE(JS,'$[1]'),'')
       ,Pos3 = nullif(JSON_VALUE(JS,'$[2]'),'')
       ,Pos4 = nullif(JSON_VALUE(JS,'$[3]'),'')
       ,Pos5 = nullif(JSON_VALUE(JS,'$[4]'),'')
       ,Pos6 = nullif(JSON_VALUE(JS,'$[5]'),'')
       ,Pos7 = nullif(JSON_VALUE(JS,'$[6]'),'')
       ,Pos8 = nullif(JSON_VALUE(JS,'$[7]'),'')
 From  YourTable A
 Cross Apply (values ('["' replace(string_escape([StringValue],'json'),'-','","') '"]') ) B(JS)

Results