Oracle正则表达式问题 [英] Issue with Oracle regex

问题描述

select regexp_substr('select count(distinct empno), count(distinct deptno) from emp',
                     'count\(distinct.*\)')
  from dual;

对于上述查询，我​​希望输出为count(distinct empno)，但是上述查询中的.*"取最后一次出现的)"，而不是第一次出现.

For the above query I want output count(distinct empno), but the ".*" in the above query is taking the last occurrence of ")" and not the first occurrence.

任何人都可以告诉我如何获得我想要的输出吗?

Can anyone please tell how to get the output I want?

推荐答案

*运算符默认情况下为'贪婪'.您允许任何数量的distinct和)之间的任何字符.并包括第一个)本身.

The * operator is 'greedy' by default. You're allowing any characters between distinct and ), in any quantity. and including the first ) itself.

按照EatÅPeach的建议，您可以使用?将其设置为非贪婪:

As EatÅPeach suggested, you can make it non-greedy with ?:

贪婪的运算符会匹配尽可能多的匹配项，同时让其余匹配项成功.要使运算符变得非贪婪，请在其后面加上非贪婪修饰符(?)

A greedy operator matches as many occurrences as possible while allowing the rest of the match to succeed. To make the operator nongreedy, follow it with the nongreedy modifier (?)

所以在这里，用.*?代替.*:

select regexp_substr(
  'select count(distinct empno), count(distinct deptno) from emp',
    'count\(distinct.*?\)')
from dual;

或者您可以指定它应该是除)和[^)]*之外的任何字符，而不是.*.

Or you can specify it should be any character except ) with [^)]* instead of .*.

select regexp_substr(
  'select count(distinct empno), count(distinct deptno) from emp',
    'count\(distinct[^)]*\)')
from dual;

这篇关于Oracle正则表达式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！