使用 SQL 将值与字符串分开

Question

我有这个要求，我们需要将值与字符串分开，格式如下

{Feature1=Value1} | {Feature2=Value2} | .. | {FeatureN=ValueN}
{12345=Gold}|{12346=Silver}

所以需要从给定的SRTing中分离特征和值。

为了分离我正在使用的管道分隔值。.

select * 
  from xmltable('r/c' passing xmltype('<r><c>' || replace('{12345=Gold}|{12346=Silver}','|','</c><c>') || '</c></r>')
columns new_str varchar2(30) path '.');
NEW_STR                    
------------------------------
{12345=Gold}                 
{12346=Silver}               

我正在编写一个 PLSQL 块，它使用上面的查询遍历每个管道单独的值。我可以将这些值存储在 PLSQL 变量中。

现在这里的另一个任务是从上面的两个字符串中获取特征和值，我在下面写 SQL

select substr ('{12345=Gold}',2, instr('{12345=Gold}', '=')-2) features from dual;
FEATURES
----------------------
12345
SELECT SUBSTR('{12345=Gold}', instr('{12345=Gold}', '=')+1, LENGTH(substr ('{12345=Gold}', instr('{12345=Gold}', '=')+1, INSTR('{12345=Gold}', '}', 2)))-1) value FROM DUAL;
VALUE
--------------
Gold

所以在这里我能够从字符串中获取特征和值......

我

正在为我的SQL寻找另一个或替代SQL，特别是最后一个，我发现它使用复杂的功能，所以如果您对上述情况有更好的想法，请发布！

如果情况不清楚，请询问

我的数据库是——

Oracle 数据库 12c 企业版版本 12.1.0.2.0 - 64 位生产

Answer 1

with line as (
      select '{12345=Gold}|{12346=Silver}|{12399=Copper}' str from dual)
select substr (parse, 2, instr(parse,'=')-2) as feature
      ,substr (parse, instr(parse,'=')+1, length(parse)-instr(parse,'=')-1 ) as value
from
   (select distinct regexp_substr(str, '[^|]+', 1, level) as parse
    from line
    connect by regexp_substr(str, '[^|]+', 1, level) is not null)

Answer 2

这是一种使用类型和名为 apex_util 的方便内置包的方法：

create type keyval_t is object (key varchar2(10), value varchar2(100));
/
create type keyval_tab_t is table of keyval_t;
/
create or replace package test_pkg is
   function keyval_tab (p_keyval_string varchar2) return keyval_tab_t;
end;
/
create or replace package body test_pkg is
   function keyval_tab (p_keyval_string varchar2) return keyval_tab_t
   is
      l_tab apex_application_global.vc_arr2;
      l_tab2 apex_application_global.vc_arr2;
      l_keyval_tab keyval_tab_t := keyval_tab_t();
      l_str long;
   begin
      -- Split string at pipe delimiters
      l_tab := apex_util.string_to_table (p_keyval_string, '|');
      -- For each {key=value} pair
      for i in 1..l_tab.count loop
         l_str := l_tab(i);
         -- Remove the {}
         l_str := ltrim (l_str, '{ ');
         l_str := rtrim (l_str, '} ');
         -- Split string into key and value
         l_tab2 := apex_util.string_to_table (l_str, '=');
         if l_tab2.count = 2 then
            l_keyval_tab.extend;
            l_keyval_tab(i) := keyval_t (l_tab2(1), l_tab2(2));
         else
            -- ?? invalid string
            null;
         end if;
      end loop;
      return l_keyval_tab;
   end;
end;
/

现在，您可以查询：

SQL> select value from table(test_pkg.keyval_tab ('{12345=Gold}|{12346=Silver}'))
  2* where key='12346';
VALUE
--------------------------------------------------------------------------------
Silver
SQL> select key from table(test_pkg.keyval_tab ('{12345=Gold}|{12346=Silver}'))
  2  where value='Gold';
KEY
----------
12345

Answer 3

使用此查询获取预期的输出。抱歉，如果替换功能太多。但这很容易。

select EXTRACTVALUE (COLUMN_VALUE, '/row/Code') code,
        EXTRACTVALUE (COLUMN_VALUE, '/row/Value') Value
from TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<rowset><row>'||replace(replace(replace(replace('{12345=Gold}|{12346=Silver}','}|{','</Value></row><row><Code>'),'{','<Code>'),'=','</Code><Value>'),'}','</Value>')||'</row></rowset>'),'/rowset/row')));

Answer 4

如果您已经将列分成行，那么使用REGEXP_REPLACE函数要做的工作相当简单。

考虑到您的 cols 现在是：

NEW_STR                    
--------------
{12345=Gold}                 
{12346=Silver}

您可以执行此 sql 以将其转换为两个不同的列：

select regexp_replace( col, '{(d+)=w+}', '1' ) as feature,
       regexp_replace( col, '{d+=(w+)}', '1' ) as value
  from testTable

将其转换为视图，然后根据需要选择包含列：

create or replace view testView as
   select regexp_replace( col, '{(d+)=w+}', '1' ) as feature,
          regexp_replace( col, '{d+=(w+)}', '1' ) as value
     from testTable

然后只需做：

select * from testView where feature = '12345'

或

Select * from testView where value = 'Gold'

如果您想将特征值转换为数字，只需使用该列上的to_number函数，如下所示：

to_number(regexp_replace( col, '{(d+)=w+}', '1' ))

请记住，为了做到这一点，您必须绝对确定它只是数字，否则您将遇到转换错误

Answer 5

您还可以使用数据透视表和REGEXP_SUBSTR

with MyStrings as
 (select '{Feature1=Value1}|{Feature2=Value2}|{FeatureN=ValueN}' Str from dual
  union all
  select '{12345=Gold}|{12346=Silver}' from dual
  )
,pivot as (
  Select Rownum Pnum
  From dual
  Connect By Rownum <= 100   
  )
SELECT rownum rn
      ,REGEXP_SUBSTR (ms.Str,'[^|]+',1,pv.pnum) TXT
  FROM MyStrings ms
      ,pivot pv
where REGEXP_SUBSTR (ms.Str,'[^|]+',1,pv.pnum) is not null