使用 SQL 将数据从大字符串提取到表中

Question

我正在尝试使用 SQL 查询从长字符串中提取数据字符串。

字符串

"'35522':{'item_id':'35522','sku':'deded','RowTotal':37.5,'qty':2"

我正在尝试创建一个从字符串中提取数据的循环查询。 所需的输出是一个包含列(item_id、sku、RowTotal、qty)的表，每一行将从相关列的上述字符串中提取。

我正在尝试创建一个可以做到这一点的函数，但目前我还没有接近。你能帮我出点什么吗？

这是我到目前为止的代码：

DECLARE @String 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}"'
DECLARE @b VARBINARY(4000) = CONVERT(varbinary(4000),@string)
DECLARE @StartPos int = 9
DECLARE @Len tinyint = 13
;WITH C (Orig, Startpos, Value) AS 
(
SELECT 
@b, @StartPos,
CONVERT(VARCHAR, SUBSTRING(@b, @StartPos, @Len))
UNION ALL
SELECT   
@b, C.Startpos + @Len,
CONVERT(VARCHAR, SUBSTRING(@b, C.StartPos + @Len, @Len)) 
FROM C
WHERE C.Startpos + @Len < = LEN(@b)
)
SELECT C.Value 
FROM c 
WHERE c.value LIKE 'item%'

Answer 1

以下方法使用STRING_SPLIT将数据拆分为行，然后替换其他字符并使用大小写表达式匹配字段值。临时表用于生成一个row_num，该可用于在生成用于将多行中的相关值分组到单个相关行中的group_num时对值进行排序

DECLARE @SampleString 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}"';
create table #temp_values (id int identity(1,1), value VARCHAR(200) );
insert into #temp_values (value) SELECT value FROM STRING_SPLIT(REPLACE(REPLACE(@SampleString,'{',''),'}',''),',');

WITH split_data AS (
SELECT id,value FROM #temp_values
),
extracted_data_raw AS (
SELECT 
id as row_num,
CASE 
WHEN value LIKE '%item_id%' THEN 1
WHEN value LIKE '%sku:%' THEN 2
WHEN value LIKE '%RowTotal:%' THEN 3
WHEN value LIKE '%qty:%' AND value NOT LIKE '%itemsQty%' THEN 4
END as type_num,
CASE 
WHEN value LIKE '%item_id%' THEN TRIM(REPLACE(value,':item_id:','')) 
END as item_id,
CASE WHEN value LIKE '%sku:%' THEN TRIM(REPLACE(value,'sku:',''))  END as sku,
CASE WHEN value LIKE '%RowTotal:%' THEN TRIM(REPLACE(value,'RowTotal:',''))   END as RowTotal,
CASE WHEN value LIKE '%qty:%' AND value NOT LIKE '%itemsQty%' THEN TRIM(REPLACE(value,'qty:',''))   END as qty,
value 
FROM split_data
)
,extracted_data_clean AS (
SELECT 
MAX(item_id) as item_id,
MAX(sku) as sku,
MAX(RowTotal) as RowTotal,
MAX(qty) as qty
FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY type_num ORDER BY row_num) group_num,
item_id,
sku,
RowTotal,
qty
FROM
extracted_data_raw
WHERE
type_num IS NOT NULL
) t
GROUP BY group_num

)
select *  from extracted_data_clean

输出：

液

item_id	sku	RowTotal	qty
35522	深线灵药	37.5	2
35527	自我护理呵护包	158	2
35531	颈部胸部嫩肤精华	21.87	1
35534	下午-恢复晚霜	23.75	1