求助：在SQL Server Studio 2016中拆分含多4变量子串的长字符串

你的输入字符串是类JSON格式（使用单引号），SQL Server 2016的OPENJSON函数可解析标准JSON，因此需先将字符串转换为标准JSON格式，再提取数据生成结构化表格。

处理单值字符串的脚本 #

如果字符串是单独的变量或常量，可使用以下脚本：

DECLARE @InputString NVARCHAR(MAX) = N"[{'actual': 0.0, 'budget': 100.00, 'subcat': 'abd', 'fcc_account': '242120'}, {'actual': 200.00, 'budget': 500.00, 'subcat': 'abc', 'fcc_account': '242123'}]";

-- 将单引号替换为双引号，转换为标准JSON
SET @InputString = REPLACE(@InputString, '''', '"');

-- 解析JSON并生成目标表格
SELECT
    ROUND(JSON_VALUE(value, '$.actual'), 2) AS Actual,
    ROUND(JSON_VALUE(value, '$.budget'), 2) AS Budget,
    JSON_VALUE(value, '$.subcat') AS Subcat,
    JSON_VALUE(value, '$.fcc_account') AS FCCAccount
FROM OPENJSON(@InputString);

处理表中列的脚本 #

如果该字符串存储在表的某一列中，使用CROSS APPLY配合OPENJSON批量处理：

SELECT
    ROUND(JSON_VALUE(REPLACE(t.YourColumnName, '''', '"'), '$.actual'), 2) AS Actual,
    ROUND(JSON_VALUE(REPLACE(t.YourColumnName, '''', '"'), '$.budget'), 2) AS Budget,
    JSON_VALUE(REPLACE(t.YourColumnName, '''', '"'), '$.subcat') AS Subcat,
    JSON_VALUE(REPLACE(t.YourColumnName, '''', '"'), '$.fcc_account') AS FCCAccount
FROM YourTableName t
CROSS APPLY OPENJSON(REPLACE(t.YourColumnName, '''', '"'));

关键说明 #

• 格式转换：REPLACE函数将单引号替换为双引号，确保字符串符合SQL Server可识别的标准JSON格式。
• JSON解析：OPENJSON会将JSON数组中的每个对象拆分为单独的行，value代表每个对象的JSON内容。
• 数值格式化：ROUND函数用于将数值保留两位小数，与你期望的输出格式一致。
• 兼容级别检查：确保数据库兼容级别至少为130（SQL Server 2016默认级别），若低于该值，执行以下语句修改：

ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 130;

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