如何将双重分隔字符串转换为SQL Server双列表格？

我有一个特定格式的数组：

• 值被双引号""包裹
• 列以逗号,分隔
• 行以分号;分隔
• 数组被大括号{}包裹

示例字符串：

{"73","2022-09-02 11:42:37.00";"118","2022-08-31 10:41:59.00";"123","2022-09-01 15:26:06.00"}

对应双列表格：

我找到了两种将其转换为SQL表格的方案：

方案一 #

DECLARE @array varchar(max)

SET @array = '{"73","2022-09-02 11:42:37.00";"118","2022-08-31 10:41:59.00";"123","2022-09-01 15:26:06.00"}'

SET @array = REPLACE(REPLACE(@array, '{', ''), '}', '')

SELECT Min(CASE b.ordinal WHEN '1' THEN b.value END) 'ID',
       Min(CASE b.ordinal WHEN '2' THEN b.value END) 'Date'
FROM STRING_SPLIT(@array, ';', 1) a
CROSS APPLY STRING_SPLIT(a.value, ',', 1) b
GROUP BY a.ordinal

方案二 #

DECLARE @array varchar(max)

SET @array = '{"73","2022-09-02 11:42:37.00";"118","2022-08-31 10:41:59.00";"123","2022-09-01 15:26:06.00"}'

SET @array = REPLACE(REPLACE(@array, '{', ''), '}', '')

SELECT PARSENAME(REPLACE(a.value, ',', '.'), 2) 'ID',
       PARSENAME(REPLACE(a.value, ',', '.'), 1) 'Date'
FROM STRING_SPLIT(@array, ';', 1) a

作为SQL Server新手，我有以下疑问：

1. 哪种方案更优？是否有更好的替代方案？我见过有人使用XML，但对XML了解有限，不确定是否适用于此场景（两列字符数不固定）。
2. 为何PARSENAME函数会自动移除值两侧的双引号？
3. 两种方案能否保证值的配对正确？（例如“73”始终与“2022-09-02 11:42:37.00”在同一行）
4. 附加问题：若转换为3列及以上的表格，哪种方案更合适？

1. 方案优劣与替代方案 #

方案二更优，逻辑简洁、代码量少，而且不需要分组和条件聚合，执行效率更高。

XML确实可以处理这种场景，但属于大材小用——XML的步骤更繁琐，需要把字符串转换成XML节点再提取值，对于你这种格式规范的简单分隔字符串来说完全没必要。如果你的值里不会出现分隔符（逗号、分号），方案二足以应对；如果是更复杂的格式（比如值本身包含分隔符），可以考虑用自定义函数或者OPENJSON，但目前你的场景用方案二就够了。

2. PARSENAME自动去双引号的真相 #

PARSENAME本身不会主动移除双引号。你看到结果没有引号，大概率是测试时的误解——比如你可以直接运行SELECT PARSENAME('"73"."2022-09-02"',2)，返回的结果是带引号的"73"。如果你的实际结果确实没有引号，要么是输入字符串里的引号本来就不存在，要么是其他隐性处理导致的，和PARSENAME无关。

3. 值配对的正确性 #

两种方案都能保证配对正确。因为你用了STRING_SPLIT的第三个参数1，这个参数会返回每个拆分部分的ordinal（顺序编号），确保行的顺序和原数组完全一致：

• 方案一中，通过a.ordinal分组，同一行的两个值会被分到同一组，再通过CASE语句提取对应列；
• 方案二中，直接拆分每一行的两个值，自然是一一对应的。
  只要原数组格式规范（每行列数固定、分隔符正确），就不会出现配对错误。

4. 多列场景的方案选择 #

如果是3列及以上，方案一更合适：

• 方案二依赖PARSENAME，而这个函数最多只能处理4个部分（对应SQL对象的4级结构：服务器.数据库.架构.表），超过4列就没法用了，而且列数越多，REPLACE加PARSENAME的写法会越来越繁琐；
• 方案一的扩展性很强，只需要增加CASE WHEN ordinal = N的分支就能支持更多列，不管多少列都能轻松处理，逻辑也清晰。

内容的提问来源于stack exchange，提问作者Leah