SQL Server 2012中如何将指定字符串拆分为多行多列

嘿，我来帮你搞定这个SQL字符串拆分的问题！你需要把用分号分隔的多组数据，每组再拆成5列（Id、Val1至Val5）的多行结果，之前的方案只能处理两列？别担心，下面这两种方法都能完美满足你的需求：

方法1：STRING_SPLIT + OPENJSON（SQL Server 2016及以上适用） #

这种方法利用SQL Server自带的字符串拆分和JSON解析函数，代码简洁高效：

DECLARE @txt nvarchar(max)='2450,10,54,kb2344,kd5433;87766,500,100,ki5332108,ow092827';

SELECT
    Id = JSON_VALUE(json_row, '$[0]'),
    Val1 = JSON_VALUE(json_row, '$[1]'),
    Val2 = JSON_VALUE(json_row, '$[2]'),
    Val3 = JSON_VALUE(json_row, '$[3]'),
    Val4 = JSON_VALUE(json_row, '$[4]')
FROM (
    -- 把每个分号分隔的子串转换成JSON数组格式
    SELECT CONCAT('["', REPLACE(value, ',', '","'), '"]') AS json_row
    FROM STRING_SPLIT(@txt, ';')
) AS split_rows
CROSS APPLY OPENJSON(json_row);

原理说明： #

1. 先用STRING_SPLIT(@txt, ';')把原字符串按分号拆分成独立的行数据；
2. 把每行的逗号分隔值转换成JSON数组格式（比如2450,10,54,kb2344,kd5433变成["2450","10","54","kb2344","kd5433"]）；
3. 通过OPENJSON解析JSON数组，再用JSON_VALUE按索引提取对应位置的列值（JSON数组是0索引，对应你要的Id、Val1到Val4）。

方法2：XML解析（兼容SQL Server 2008及以上版本） #

如果你的SQL Server版本低于2016，没法用STRING_SPLIT，那XML解析的方法就很合适：

DECLARE @txt nvarchar(max)='2450,10,54,kb2344,kd5433;87766,500,100,ki5332108,ow092827';

-- 先把分号分隔的结构转换成XML行节点
DECLARE @xml_data xml = '<rows><row>' + REPLACE(@txt, ';', '</row><row>') + '</row></rows>';

SELECT
    Id = value_nodes.value('(.)[1]', 'nvarchar(100)'),
    Val1 = value_nodes.value('(.)[2]', 'nvarchar(100)'),
    Val2 = value_nodes.value('(.)[3]', 'nvarchar(100)'),
    Val3 = value_nodes.value('(.)[4]', 'nvarchar(100)'),
    Val4 = value_nodes.value('(.)[5]', 'nvarchar(100)')
FROM @xml_data.nodes('/rows/row') AS row_nodes(row)
CROSS APPLY (
    -- 把每行的逗号分隔值转换成XML值节点
    SELECT CAST('<v>' + REPLACE(row_nodes.row.value('.', 'nvarchar(max)'), ',', '</v><v>') + '</v>' AS xml)
) AS value_xml(xml_content)
CROSS APPLY value_xml.xml_content.nodes('/v') AS value_list(value_nodes);

原理说明： #

1. 先把原字符串的分号替换成XML的行标签，生成完整的XML结构；
2. 对每个行节点，把逗号分隔值转换成独立的XML值节点；
3. 通过nodes()方法提取所有值节点，再按位置（XML是1索引）获取对应列值。

两种方法执行后都会得到你想要的结果：

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