SQL Server TSQL Split Variable Pairs into Columns?

8nuwlpux 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(89)

Using SQL Server 2022, I'm trying to figure out how to use STRING_SPLIT in a set-based way to convert a table column where all rows have a variable number of key/value pairs into a table without duplicates, similar to T-SQL split string but not quite.

create table #mashed (mashedtext nvarchar(1600))

insert into #mashed 
values ('"id":"one", "code":"aaa", "dev":"yes"')
insert into #mashed 
values ('"id":"two", "code":"bbb", "dev":"yes"')
insert into #mashed 
values ('"id":"three", "code":"ccc", "dev":"no"')
insert into #mashed 
values ('"id":"three", "code":"bbb", "help":"no" , "rid":"6"')

Desired shape of output

key    value
----------------
   id     one
   id     two
   id     three
   code   aaa
   code   bbb
   code   ccc
   dev    yes
   dev    no
   help   no
   rid    6

This is clearly wrong:

SELECT value 
FROM STRING_SPLIT (SELECT mashedtext FROM #mashed, ',')

So what is the right way to do this without RBAR? I am sure there are two splits needed, once to get the pairs and again on each pair.

Thanks.

sql-server

来源：https://stackoverflow.com/questions/76808345/tsql-split-variable-pairs-into-columns

2条答案

按热度按时间

wwtsj6pe1#

The stored text data is almost a valid JSON object, so you may try to fix it and parse it with OPENJSON() :

SELECT DISTINCT j.[key], j.[value]
FROM #mashed m
CROSS APPLY OPENJSON(CONCAT('{', m.mashedtext, '}')) j

If you want to use STRING_SPLIT() you need to use the third optional parameter in STRING_SPLIT() (introduced in SQL Server 2022) to get the ordinal position of the each substring:

SELECT DISTINCT s2.*
FROM #mashed m
CROSS APPLY STRING_SPLIT(m.mashedtext, ',') s1
CROSS APPLY (
   SELECT
      [key] = MAX (REPLACE(CASE WHEN [ordinal] = 1 THEN [value] END, '"', '')),
      [value] = MAX (REPLACE(CASE WHEN [ordinal] = 2 THEN [value] END, '"', ''))
  FROM STRING_SPLIT(TRIM(s1.[value]), ':', 1)
) s2

Result (without order):

key	value
code	aaa
code	bbb
code	ccc
dev	no
dev	yes
help	no
id	one
id	three
id	two
rid	6

赞(0）回复(0）举报 2023-08-02

v8wbuo2f2#

STRING_SPLIT() to split into rows and then perform string parsing to extract the required key and value

SELECT m.mashedtext, v.[value], kv.[key], kv.[val]
FROM   #mashed m
       CROSS APPLY STRING_SPLIT(mashedtext, ',') s
       CROSS APPLY
       (
           -- remove leading spaces
           select [value] = ltrim(s.[value])
       ) v
       CROSS APPLY
       (     
           -- find position of colon
           select p = charindex(':', v.[value])
       ) p
       CROSS APPLY
       (
           -- get the required key and val
           select [key] = replace(left(v.value, p - 1), '"', ''),
                  [val] = replace(right(v.value, len(v.value) - p), '"', '')
       ) kv

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server TSQL Split Variable Pairs into Columns?

2条答案

相关问题

热门标签

最新问答