Oracle 11g通过正则表达式获取所有匹配的事件 [英] Oracle 11g get all matched occurrences by a regular expression

问题描述

我正在使用Oracle 11g，我想使用REGEXP_SUBSTR匹配给定模式的所有匹配项. 例如

I'm using Oracle 11g and I would like to use the REGEXP_SUBSTR to match all the occurrences for a given pattern. For example

 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.

returns only the first match Ta233141 but I would like to return the other occurrences that match the regex, meaning Ta233142 Ta233147 Ta233148.

推荐答案

REGEXP_SUBSTR仅返回一个值.您可以将您的字符串转换为伪表，然后查询该字符串以查找匹配项.目前有一种基于XML的方法可以使我逃脱，但是只要您只有一个源字符串，使用connect-by即可:

REGEXP_SUBSTR only returns one value. You could turn your string into a pseudo-table and then query that for matches. There's an XML-based way of doing this that escapes me at the moment, but using connect-by works, as long as you only have one source string:

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            

...，您可以使用原始模式的稍微简化的版本对其进行过滤:

... and you can filter that with a slightly simpler version of your original pattern:

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             

哪个不是很漂亮，但是两个字段中都没有包含多个值.

Which isn't very pretty, but neither is holding multiple values in one field.

这篇关于Oracle 11g通过正则表达式获取所有匹配的事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！