Разбиение значения символьного столбца на пары ключ-значение по нескольким разделителям

Question

Был такой вопрос, где значения символьной переменной в PL/SQL контексте такого формата:

номер1;текст1/nномер2;текст2/nномерN;текстN/n

Надо было разбить на токены по нескольким разделителям, чтобы получилась коллекция записей (номер1,текст1).

Если предположить, что значения подобного формата хранятся в таблице, например:

create table params (id, text) as
    select 1, 'key1=>val1;key2=>val2;key3=>val3' from dual union all 
    select 2, 'key4=>val4;key5=>val5;key6=>val6' from dual union all 
    select 3, 'key7=>val7;key8=>val8;key9=>val9' from dual

То очевидно, что эффективней было бы одним запросом получить коллекцию, элементы которой - пара ключ-значение:

create or replace package pack1 as
    type pairr is record (id int, key varchar2 (8), value varchar2 (8));
    type pairt is table of pairr;
end;

И дальше работать с этой коллекцией. То есть, ожидаемый результат такой:

        ID KEY      VALUE   
---------- -------- --------
         1 key1     val1    
         1 key2     val2    
[...]

Как это можно сделать?

Answer 1

Вот как это реализовать в SQL Server 2016 и более поздних версиях. Это не Oracle, но, возможно даст некоторые идеи.

T-SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, KeyValueList VARCHAR(1024));
INSERT INTO @tbl (KeyValueList) VALUES
('key1=>val1;key2=>val2;key3=>val3'),
('key4=>val4;key5=>val5;key6=>val6'),
('key7=>val7;key8=>val8;key9=>val9');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ';'
    , @kv CHAR(2) = '=>';

SELECT ID, value AS [JustToSee]
    , LEFT(value, CHARINDEX(@kv, value)-1) AS [Key]
    , SUBSTRING(value, CHARINDEX(@kv, value)+ LEN(@kv), LEN(value)) AS [Value]
FROM @tbl
    CROSS APPLY STRING_SPLIT(KeyValueList, @separator);

результат

+----+------------+------+-------+
| ID | JustToSee  | Key  | Value |
+----+------------+------+-------+
|  1 | key1=>val1 | key1 | val1  |
|  1 | key2=>val2 | key2 | val2  |
|  1 | key3=>val3 | key3 | val3  |
|  2 | key4=>val4 | key4 | val4  |
|  2 | key5=>val5 | key5 | val5  |
|  2 | key6=>val6 | key6 | val6  |
|  3 | key7=>val7 | key7 | val7  |
|  3 | key8=>val8 | key8 | val8  |
|  3 | key9=>val9 | key9 | val9  |
+----+------------+------+-------+

T-SQL #2, XML and XQuery, в SQL Server 2008 и более поздних версиях

;WITH rs AS
(
    SELECT * 
        , TRY_CAST('<root><r><![CDATA[' + 
                REPLACE(KeyValueList, @separator, ']]></r><r><![CDATA[') + 
              ']]></r></root>' AS XML) AS xmldata
    FROM @tbl
)
, cte AS
(
    SELECT ID, c.value('(./text())[1]','VARCHAR(30)') AS token
    FROM rs
        CROSS APPLY xmldata.nodes('/root/r') AS t(c)
)
SELECT * 
    , LEFT(token, CHARINDEX(@kv, token)-1) AS [Key]
    , SUBSTRING(token, CHARINDEX(@kv, token) + LEN(@kv), LEN(token)) AS [Value]
FROM cte;

Answer 2

Одно из простых решений, передавать значение столбца в xmlTable и обработать его FLWOR выражением. Запрос будет выглядеть так:

var rc refcursor
declare
    pairs pack1.pairt;
begin
    select id, key, val bulk collect into pairs 
    from params, xmltable ('
        for $row in tokenize (., ";")
        return element el {
            attribute key { normalize-space (tokenize ($row, "=>")[1]) }, 
            attribute val { normalize-space (tokenize ($row, "=>")[2]) }}'
        passing text columns
            key varchar2 (10) path '@key', val varchar2 (10) path '@val');
    
    open :rc for select * from table (pairs);
end;
/

Он вернёт ожидаемый результат:

        ID KEY      VALUE   
---------- -------- --------
         1 key1     val1    
         1 key2     val2    
[...]
         3 key9     val9

Answer 3

Код из принятого ответа по ссылке на изначальный вопрос, вполне пригоден и для SQL контекста.

Вставил его в конвейерную функцию и поправил/добавил пару строк. Выглядит это так:

create or replace package pack1 as
    type pairr is record (id int, key varchar2 (8), value varchar2 (8));
    type pairt is table of pairr;
    function getParams (id int := null) return pairt pipelined;  
end;
/
create or replace package body pack1 as
    function getParams (id int := null) return pairt pipelined is
        pattern constant varchar2 (64) := '(\w+)=>(\w+);?';
        pair pack1.pairr;
        counter int;
    begin
        for r in (
            select * from params 
            where id = coalesce (getParams.id, id)
            ) loop
            counter := 0;
            <<split>> loop counter := counter+1;
                exit split when regexp_substr (r.text, pattern, 1, counter) is null;
                pair.id    := r.id;
                pair.key   := regexp_substr (r.text, pattern, 1, counter, null, 1);
                pair.value := regexp_substr (r.text, pattern, 1, counter, null, 2);
                pipe row (pair);
            end loop;
        end loop;
        return;
    end;
end;
/

Работает:

select * from pack1.getParams (id=>1);

        ID KEY      VALUE   
---------- -------- --------
         1 key1     val1    
         1 key2     val2    
         1 key3     val3

БЛОГ НА HUSL

Разбиение значения символьного столбца на пары ключ-значение по нескольким разделителям

Ответы (3 шт):