如何在MS SQL中将特定格式字符串拆分至4列？

问题背景 #

有格式如下的字符串：s:6:"module";s:11:"leadCapture";（备注：开头的s无实际意义）

我尝试使用STRING_SPLIT函数拆分字符串：

SELECT value 
into temp
FROM STRING_SPLIT('s:6:"module";s:11:"leadCapture";s:6:"action";s:5:"save2";', ';');

select * from temp;

当前输出 #

行1: s:6:"module"
行2: s:11:"leadCapture"
行3: s:6:"action"
行4: s:5:"save2"

期望输出 #

6 - 列1, 
module - 列2, 
11 - 列3, 
leadcapture - 列4. 

解决方案 #

可以通过字符串提取函数结合行号处理，将拆分后的内容转换成目标格式：

方法1：生成逐行匹配的结果集 #

WITH split_data AS (
    SELECT 
        value,
        -- 提取数字部分
        SUBSTRING(value, CHARINDEX(':', value)+1, CHARINDEX(':"', value)-CHARINDEX(':', value)-1) AS num,
        -- 提取引号内的名称并转小写
        LOWER(SUBSTRING(value, CHARINDEX(':"', value)+2, CHARINDEX('"', value, CHARINDEX(':"', value)+2)-CHARINDEX(':"', value)-2)) AS name,
        -- 生成行序号用于匹配列位置
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM STRING_SPLIT('s:6:"module";s:11:"leadCapture";s:6:"action";s:5:"save2";', ';')
    WHERE value <> '' -- 过滤拆分后产生的空值
)
-- 按行序号映射到目标列并合并结果
SELECT CONCAT(num, ' - 列', (rn-1)*2 +1) AS 结果 FROM split_data
UNION ALL
SELECT CONCAT(name, ' - 列', (rn-1)*2 +2) AS 结果 FROM split_data
-- 按列序号排序
ORDER BY 
    CASE 
        WHEN ISNUMERIC(LEFT(结果,1)) = 1 THEN (rn-1)*2 +1 
        ELSE (rn-1)*2 +2 
    END;

方法2：生成横向列格式结果 #

如果需要将结果作为横向列展示，可使用条件聚合：

WITH split_data AS (
    SELECT 
        value,
        CAST(SUBSTRING(value, CHARINDEX(':', value)+1, CHARINDEX(':"', value)-CHARINDEX(':', value)-1) AS INT) AS num,
        LOWER(SUBSTRING(value, CHARINDEX(':"', value)+2, CHARINDEX('"', value, CHARINDEX(':"', value)+2)-CHARINDEX(':"', value)-2)) AS name,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM STRING_SPLIT('s:6:"module";s:11:"leadCapture";s:6:"action";s:5:"save2";', ';')
    WHERE value <> ''
)
SELECT
    MAX(CASE WHEN rn=1 THEN num END) AS 列1,
    MAX(CASE WHEN rn=1 THEN name END) AS 列2,
    MAX(CASE WHEN rn=2 THEN num END) AS 列3,
    MAX(CASE WHEN rn=2 THEN name END) AS 列4,
    MAX(CASE WHEN rn=3 THEN num END) AS 列5,
    MAX(CASE WHEN rn=3 THEN name END) AS 列6,
    MAX(CASE WHEN rn=4 THEN num END) AS 列7,
    MAX(CASE WHEN rn=4 THEN name END) AS 列8
FROM split_data;

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