Oracle 11g使用正则表达式获取所有匹配的结果

Question

我正在使用Oracle 11g，希望使用REGEXP_SUBSTR来匹配给定模式的所有出现次数。例如：

 SELECT
  REGEXP_SUBSTR('Txa233141b Ta233141 Ta233142 Ta233147 Ta233148',
  '(^|\s)[A-Za-z]{2}[0-9]{5,}(\s|$)') "REGEXP_SUBSTR"
  FROM DUAL;

只返回第一个匹配项 Ta233141，但我想返回与正则表达式匹配的其他出现次数，也就是 Ta233142、Ta233147 和 Ta233148。

- florins

Answer 1

REGEXP_SUBSTR只返回一个值。您可以将字符串转换为伪表，然后查询其中的匹配项。有一种基于XML的方法可以实现这一点，但我现在无法想起来，但是只要您只有一个源字符串，使用connect-by就可以。

SELECT REGEXP_SUBSTR(str, '[^ ]+', 1, LEVEL) AS substr
FROM (
    SELECT 'Txa233141b Ta233141 Ta233142 Ta233147 Ta233148' AS str FROM DUAL
)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, '[^ ]+')) + 1;

...给你：

SUBSTR             
--------------------
Txa233141b           
Ta233141             
Ta233142             
Ta233147            
Ta233148            

...而您可以使用稍微简化的原始模式来过滤它:

SELECT substr
FROM (
    SELECT REGEXP_SUBSTR(str, '[^ ]+', 1, LEVEL) AS substr
    FROM (
        SELECT 'Txa233141b Ta233141 Ta233142 Ta233147 Ta233148' AS str
        FROM DUAL
    )
    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, '[^ ]+')) + 1
)
WHERE REGEXP_LIKE(substr, '^[A-Za-z]{2}[0-9]{5,}$');

SUBSTR             
--------------------
Ta233141             
Ta233142             
Ta233147             
Ta233148             

这并不是很美观，但在一个字段中保存多个值也是不美观的。

- Alex Poole

---

这段代码没有返回Ta233148。要得到它，请将“CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, '[^ ]+'))”更改为“CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, '[^ ]+'))+1”。 - sakthi

直接使用此代码与除简单的分隔符字符以外的任何内容一起将返回过多的结果。 将 LENGTH(REGEXP_REPLACE(str, '[^ ]+'))+1 替换为 REGEXP_COUNT(str, '[^ ]+')，您将获得预期的结果。 - bkqc

Answer 2

这篇文章略有滞后，但我需要基本相同的功能，并且找不到好的代码片段。我需要在一个表的自由文本列中搜索一些术语并收集它们。鉴于这可能对他人有用，我提供了一个基于此问题的版本。虽然REGEXP_SUBSTR只返回一个值，但Oracle还提供了REGEXP_COUNT来告诉您给定字符串中有多少匹配项，因此您可以将其与索引列表连接以选择每个匹配项（以下是来自某个“源表”的免费文本查询示例）。

DEFINE MATCH_EXP = "'(^|\s)[A-Za-z]{2}[0-9]{5,}'"

WITH source_table
     -- Represents some DB table with a 'free_text' column to be checked.
     AS (       ( SELECT 'Txa233141 Ta233141 Ta232 Ta233142 Ta233141 Ta233148'
                             AS free_text FROM dual )
          UNION ( SELECT 'Other stuff PH33399 mixed in OS4456908843 this line'
                             AS free_text FROM dual )
        )
   , source
     -- For some table, select rows of free text and add the number of matches
     -- in the line.
     AS ( SELECT cnt
               , free_text
          FROM ( SELECT RegExp_Count(free_text, &MATCH_EXP) AS cnt
                      , free_text 
                 FROM source_table )
          WHERE cnt > 0 )
   , iota
     -- Index generator
     AS ( SELECT RowNum AS idx
          FROM dual
          CONNECT BY RowNum <= ( SELECT Max(cnt) FROM source ) )
-- Extract the unique 'cnt' matches from each line of 'free_text'.
SELECT UNIQUE
       RegExp_SubStr(s.free_text, &MATCH_EXP, 1, i.idx) AS result
FROM   source s
  JOIN iota i
    ON ( i.idx <= s.cnt )
ORDER BY result ASC
;

它具有任何所选行列表的工作优势，并且最小化使用CONNECT BY（因为这可能非常慢）。

- Steven Cochran

Answer 3

如何添加一个循环遍历并返回所有值的功能？

create or replace function regexp_substr_mr (
  p_data clob,
  p_re varchar
  )
return varchar as
  v_cnt number;
  v_results varchar(4000);
begin
  v_cnt := regexp_count(p_data, p_re, 1,'m');
  if v_cnt < 25 then
    for i in 1..v_cnt loop
      v_results := v_results || regexp_substr(p_data,p_re,1,i,'m') || chr(13) || chr(10);
    end loop;
  else 
    v_results := 'WARNING more than 25 matches found';
  end if;

  return v_results;
end;

然后只需将函数作为选择查询的一部分调用。

- PhilJ

---

原则上，我更喜欢这种方法，因为我认为它比已接受的解决方案具有更广泛的应用。但是，我会通过传递p_data、p_re和flags并创建一个类型，例如“create or replace type mytype is table of varchar2(4000)”，并从函数中返回该类型来改进它。然后，可以将其嵌入到SQL中，如下所示：select A.id,X.column_value from someTable A cross join table(regexp_substr_mr(A.textToSearch,'regexp') X - Pancho

Answer 4

我正在修复@Alex Poole的答案，以支持多行源代码，并更快地执行：

with templates as (select '\w+' regexp from dual)
select 
    regexp_substr(str, templates.regexp, 1, level) substr
from (
    select 1 id, 'Txa233141b Ta233141 Ta233142 Ta233147 Ta233148' as str from dual
    union
    select 2 id, '2 22222222222222Ta233141 2Ta233142 2Ta233147' as str from dual
    union
    select 3 id, '3Txa233141b 3Ta233141 3Ta233142' as str from dual
)
join templates on 1 = 1
connect by 
    id = connect_by_root id
    and regexp_instr(str, templates.regexp, 1, level) > 0
order by id, level

源代码行：

ID  STR                                             
--  ----------------------------------------------  
1   Txa233141b Ta233141 Ta233142 Ta233147 Ta233148  
2   2 22222222222222Ta233141 2Ta233142 2Ta233147    
3   3Txa233141b 3Ta233141 3Ta233142                 

结果：

Txa233141b              
Ta233141                
Ta233142                
Ta233147                
Ta233148                
2                       
22222222222222Ta233141  
2Ta233142               
2Ta233147               
3Txa233141b             
3Ta233141               
3Ta233142               

- David E. Veliev

---

您的查询返回错误："ORA-30007：在START WITH或CONNECT BY条件中不支持CONNECT BY ROOT运算符"。如何修复？ - tungns

Answer 5

SELECT  
  LISTAGG(REGEXP_SUBSTR('Txa233141b Ta233141 Ta233142 Ta233147 Ta233148', '(^|\s)[A-Z]{2}[0-9]{5,}',1,level) 
         ) WITHIN GROUP(ORDER BY 1)
  AS REGEXP_SUBSTR
FROM DUAL
CONNECT BY level<=regexp_count('Txa233141b Ta233141 Ta233142 Ta233147 Ta233148','(^|\s)[A-Z]{2}[0-9]{5,}')
;

- Sergio Barbosa

---

您的答案可以通过提供更多支持信息来改进。请 [编辑] 添加进一步的细节，例如引用或文档，以便其他人可以确认您的答案是否正确。您可以在 帮助中心 中找到有关如何编写好答案的更多信息。 - Community

Answer 6

基于 @David E. Veliev 的 答案，这是针对多行输入的查询。如果以下查询适用于您，请考虑投票支持原始答案。

SELECT SUBSTR
  FROM (WITH TEMPLATES AS (SELECT '\w+' REGEXP FROM DUAL)
         SELECT ID,
                CONNECT_BY_ROOT ID CBR,
                LEVEL LVL,
                REGEXP_SUBSTR(STR, TEMPLATES.REGEXP, 1, LEVEL) SUBSTR
           FROM (SELECT 1 ID,
                        'Txa233141b Ta233141 Ta233142 Ta233147 Ta233148' AS STR
                   FROM DUAL
                 UNION
                 SELECT 2 ID,
                        '2 22222222222222Ta233141 2Ta233142 2Ta233147' AS STR
                   FROM DUAL
                 UNION
                 SELECT 3 ID,
                        '3Txa233141b 3Ta233141 3Ta233142' AS STR
                   FROM DUAL)
           JOIN TEMPLATES
             ON 1 = 1
         CONNECT BY REGEXP_INSTR(STR, TEMPLATES.REGEXP, 1, LEVEL) > 0)
          WHERE ID = CBR
          GROUP BY ID,
                   CBR,
                   LVL,
                   SUBSTR
          ORDER BY ID,
                   LVL;

输入：

ID  STR
==  ==============================================
1   Txa233141b Ta233141 Ta233142 Ta233147 Ta233148
2   2 22222222222222Ta233141 2Ta233142 2Ta233147
3   3Txa233141b 3Ta233141 3Ta233142

输出：

SUBSTR
======================
Txa233141b
Ta233141
Ta233142
Ta233147
Ta233148
2
22222222222222Ta233141
2Ta233142
2Ta233147
3Txa233141b
3Ta233141
3Ta233142

- tungns

Answer 7

以下是您问题的简单解决方案。

SELECT REGEXP_SUBSTR('Txa233141b Ta233141 Ta233142 Ta233147 Ta233148',
  '([a-zA-Z0-9]+\s?){1,}') "REGEXP_SUBSTR"
  FROM DUAL;

- Mohammed Zubair

---

这只是返回原始字符串的代码；它包括Txa233141b，即使它以三个非数字字符开头，而不是OP模式所需的两个。 - Alex Poole