更新的问题-Oracle case语句，用于提取与单行中的子字符串关联的所有事件的值。

Question

我想要写一个case语句，它可以从一个名为details的列中提取特定子字符串的值，该列有多个[address]出现，是否可以与[address]一起使用？

列中的样本数据：

[address]:kattengat 28
[address]:1012 SZ
[address]: Amsterdam

The below SQL only outputs:

kattengat 28

Select case when to_number(instr(details),'[address')>0 then substr(details,REGEXP_INSTR(details,'address',1,1)+8,instr(substr(details,REGEXP_INSTR(details,'address',1,1)+8,'[')-1)) else '' end from table_name;

Expected output is : 
kattengat 28 1012 SZ Amsterdam

创建table语句：

Create table test (id number(10), details clob);

插入声明：

insert into test (id, details) values (1,to_clob ('[ADDRESS    ] kattengat 28
             [NAME       ] ALEX
             [PHONE      ] 65438
             [ADDRESS    ] 1012 SZ
             [DOB        ] 1st Jan 1998
             [ADDRESS    ] Amsterdam')):

--请注意，我不想连接并添加语句，而是要寻找一个解决方案，该解决方案可以根据单行中子字符串出现的次数提取与子字符串地址相关联的值。

Answer 1

这里有一个选择：

SQL> with test (col) as
  2    (select '[address]:kattengat 28
  3  [address]:1012 SZ
  4  [address]: Amsterdam' from dual
  5    )
  6  select trim(replace(regexp_substr(replace(col, chr(10), '#'), '[^#]+', 1, column_value), '[address]:', '')) result
  7  from test cross join
  8    table(cast(multiset(select level from dual
  9                        connect by level <= regexp_count(col, ':')
 10                       ) as sys.odcinumberlist));

RESULT
--------------------------------------------------------------------------------
kattengat 28
1012 SZ
Amsterdam

SQL>

是干什么的呢？

示例数据行#1 -5-示例数据行#6：

• regexp_substr代码的一部分负责将源列值拆分为单独的行(
• )，它不影响原始值，而是将新行字符(chr(10))替换为# (第二个replace)，并将该字符用作regexp_substr

的分隔符。

source

• trim中的第一个replace删除了前导/尾随的空字符串(因为"Amsterdam"

• lines #8 - 10前面的那个是为了从最终结果中删除重复的值)(如我的例子，如果源表不包含一行的话)。如果确实如此，那么代码可以稍微简化一些。

使用稍后发布的示例数据(顺便问一下，您是否确定前面有空格等等？)我想不是！)：

SQL> select * from test;

        ID DETAILS
---------- --------------------------------------------------
         1 [ADDRESS    ] kattengat 28
           [NAME       ] ALEX
           [PHONE      ] 65438
           [ADDRESS    ] 1012 SZ
           [DOB        ] 1st Jan 1998
           [ADDRESS    ] Amsterdam

我之前发布的代码，因为以前的address是小写的，所以稍微修改了一下，方括号中没有空格，还有冒号)：

SQL> with temp as
  2    (select trim(replace(regexp_substr(replace(details, chr(10), '#'), '[^#]+', 1, column_value), '[ADDRESS    ]', '')) result
  3     from test cross join
  4     table(cast(multiset(select level from dual
  5                         connect by level <= regexp_count(details, '\[')
  6                        ) as sys.odcinumberlist))
  7    )
  8  select *
  9  from temp
 10  where instr(result, '[') = 0;

RESULT
--------------------------------------------------------------------------------
kattengat 28
1012 SZ
Amsterdam

SQL>

如果希望在一行中获得结果，可以将该查询返回的聚合值作为

SQL> with temp as
  2    (select trim(replace(regexp_substr(replace(details, chr(10), '#'), '[^#]+', 1, column_value), '[ADDRESS    ]', '')) result,
  3            column_value cv
  4     from test cross join
  5     table(cast(multiset(select level from dual
  6                         connect by level <= regexp_count(details, '\[')
  7                        ) as sys.odcinumberlist))
  8    )
  9  select listagg (result, ', ') within group (order by cv) final_result
 10  from temp
 11  where instr(result, '[') = 0;

FINAL_RESULT
--------------------------------------------------------------------------------
kattengat 28, 1012 SZ, Amsterdam

SQL>