sql - Issue with Oracle regex -

sql - Issue with Oracle regex -

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

for above query want output count(distinct empno), ".*" in above query taking lastly occurrence of ")" , not first occurrence.

can please tell how output want?

the * operator 'greedy' default. you're allowing characters between distinct , ), in quantity. , including first ) itself.

as eatÅpeach suggested, can create non-greedy ?:

a greedy operator matches many occurrences possible while allowing rest of match succeed. create operator nongreedy, follow nongreedy modifier (?)

so here, .*? instead of .*:

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

or can specify should character except ) [^)]* instead of .*.

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

sql regex oracle oracle11g