按分隔符将 T-SQL 字符串分割成列？

Question

我有一个只有一列的表格，使用连字符分隔的八个值，其中一些为空。我试图将此字符串拆分为多列，每个值对应于分隔字符串的位置：

Table1:

记录	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

期望结果：

记录	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

我尝试了一系列复杂的SUBSTRING/CHARINDEX函数，虽然可以工作，但我想知道是否有更合适的解决方案？当我尝试使用PARSENAME函数时，它只返回NULL值，因为字符串中并不是每个位置都有值。

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

- Stetson Reed

Answer 1

如您所知，parsename() 仅适用于4个位置。

这里是一个 JSON 选项（假设版本为 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)

结果

这是一张图片，无法翻译。

- John Cappelletti