提取词语的预言

Question

假设我有字符串

Str = 'Aaa,Bbb,Abb,Ccc' 

我想把上述STR两个部分分开如下

Str1 = 'Aaa,Abb' 

Str2 = 'Bbb,Ccc' 

即在STR开始与任何字A应该在str2中休息。

如何使用Oracle查询来实现这一目标？

Answer 1

这是str中任何以A开头的单词都应该在str1中休息，全部在str2中休息。

为了实现它在纯SQL，我会用以下内容：

• REGEXP_SUBSTR
• LISTAGG
• SUBSTR
• 内嵌视图

因此，首先我将使用这里演示的技术分割逗号分隔的字符串，如Split single comma delimited string into rows所示。

然后，我将按顺序使用LISTAGG将它们聚合。

例如，

SQL> WITH 
    2 t1 AS (
    3 SELECT 'Aaa,Bbb,Abb,Ccc' str FROM dual 
    4  ), 
    5 t2 AS (
    6 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 
    7 FROM t1 
    8 CONNECT BY LEVEL <= regexp_count(str, ',')+1 
    9 ORDER BY str 
10  ) 
11 SELECT 
12 (SELECT listagg(str, ',') WITHIN GROUP(
13 ORDER BY NULL) str1 
14 FROM t2 
15 WHERE SUBSTR(str, 1, 1)='A' 
16 ) str1, 
17 (SELECT listagg(str, ',') WITHIN GROUP(
18 ORDER BY NULL) str 
19 FROM t2 
20 WHERE SUBSTR(str, 1, 1)<>'A' 
21 ) str2 
22 FROM dual 
23/

STR1  STR2 
---------- ---------- 
Aaa,Abb Bbb,Ccc 

SQL> 

的WITH条款只是为了演示的目的，您的真实场景中，与第取出并直接使用你的表名。尽管使用WITH子句看起来很整齐。

Answer 2

使用regext表达式和ListAg函数。

注意：LISTAGG函数自Oracle 11g起可用！

select listagg(s.name, ',') within group (order by name) 
    from (select regexp_substr('Aaa,Bbb,Abb,Ccc,Add,Ddd','[^,]+', 1, level) name from dual 
     connect by regexp_substr('Aaa,Bbb,Abb,Ccc,Add,Ddd', '[^,]+', 1, level) is not null) s 
group by decode(substr(name,1,1),'A', 1, 0); 

Answer 3

该查询为您提供了两种不同的行所需的输出：

with temp as (select trim (both ',' from 'Aaa,Bbb,Abb,Ccc') as str from dual), 
    base_table as 
     ( select trim (regexp_substr (t.str, 
              '[^' || ',' || ']+', 
              1, 
              level)) 
         str 
       from temp t 
      connect by instr (str, 
          ',', 
          1, 
          level - 1) > 0), 
    ult_table as 
     (select str, 
       case upper (substr (str, 1, 1)) when 'A' then 1 else 2 end 
        as l 
      from base_table) 
select listagg (case when l = 1 then str else null end, ',') 
      within group (order by str) 
      str1, 
     listagg (case when l = 2 then str else null end, ',') 
      within group (order by str) 
      str2 
    from ult_table; 

输出

  L STR 
---------- -------------------------------------------------------------------------------- 
     1 Aaa,Abb                   
     2 Bbb,Ccc