如何使用SQL从长字符串中提取数据生成结构化表格并实现循环查询逻辑？

嘿，我看你正在尝试从这个半结构化的商品字符串里提取数据，当前用固定长度截取的方法确实不太靠谱——毕竟每个字段的内容长度都可能变化，很容易出错。咱们换个更稳健的思路：把这个类JSON字符串修正成标准JSON格式，然后用SQL的JSON解析工具来提取数据，轻松得到结构化表格。

步骤1：修正字符串为标准JSON格式 #

你的目标字符串有几个不规范的地方：

• 键名没有双引号（比如item_id应该是"item_id"）
• 字符串类型的字段值没有双引号（比如deep-line-elixir应该是"deep-line-elixir"）
• 开头和结尾的格式不对（多了冒号，缺少数组包裹）

我们可以用REPLACE函数批量修正这些问题：

DECLARE @OriginalString VARCHAR(4000) = '{:{item_id:35522,sku:deep-line-elixir,RowTotal:37.5,qty:2}, :{item_id:35527,sku:self-care-pamper-pack,RowTotal:158,qty:2}, :{item_id:35531,sku:neck-chest-rejuvenating-serum,RowTotal:21.87,qty:1}, :{item_id:35534,sku:pm-recovery-night-cream,RowTotal:23.75,qty:1},couponCode:,itemsQty:6}'

-- 修正为标准JSON的基础结构
DECLARE @ValidJSON NVARCHAR(4000) = 
REPLACE(
    REPLACE(
        REPLACE(
            REPLACE(@OriginalString, '{:{', '{"items":[{"'),
            '}, :{', '},{"'),
        ',couponCode:', ',"couponCode":""'),
    ',itemsQty:6}', ',"itemsQty":6}]')

-- 给所有键名加上双引号，给sku值补充闭合双引号
SET @ValidJSON = REPLACE(REPLACE(REPLACE(REPLACE(@ValidJSON, 'item_id:', '"item_id":'), 'sku:', '"sku":"'), 'RowTotal:', '","RowTotal":'), 'qty:', '"qty":')

步骤2：用OPENJSON解析JSON并生成结构化表格 #

修正后的标准JSON可以直接用OPENJSON函数解析，指定需要提取的字段和对应数据类型：

SELECT 
    JSON_VALUE(item.value, '$.item_id') AS item_id,
    JSON_VALUE(item.value, '$.sku') AS sku,
    CAST(JSON_VALUE(item.value, '$.RowTotal') AS DECIMAL(10,2)) AS RowTotal,
    CAST(JSON_VALUE(item.value, '$.qty') AS INT) AS qty
FROM OPENJSON(@ValidJSON, '$.items') AS item

运行结果： #

为什么这个方法更好？ #

• 鲁棒性强：不像固定长度截取依赖字段位置，JSON解析是基于键名的，就算字段顺序变化也能正确提取数据。
• 易维护：如果以后要添加新字段，只需要在SELECT里加一行JSON_VALUE即可，不需要调整截取逻辑。
• 兼容性好：SQL Server 2016及以上版本都支持OPENJSON函数，绝大多数现代环境都能运行。

对比你原来的CTE方法：它只能按固定长度截取片段，没法关联同一个商品的多个字段，而且一旦某个字段值长度变化（比如sku名称变长），整个截取逻辑就会失效，显然不适合处理这种半结构化数据。

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