oracle regexp_substr to fetch string

Question

I have a sql query like

select count(distinct empno), count(distinct(deptno empname empid )) from emp

I want to fetch first occurrence of count and its content like:

count(distinct empno)

I have tried the following sql statement but its not working,

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

REGEXP_SUBSTR('SELECTCOUNT(DISTINCTEMPNO),COUN ---------------------------------------------- count(distinct empno), count(distinct(deptno ))

I want output to return like :

REGEXP_SUBSTR('SELECTCOUNT(DISTINCTEMPNO)

count(distinct empno),

Regular expression should not match second ) closing bracket and it should match the 1st closing bracket.

Answer 1

* in your regex ates everything, you need to make the regex engine to match the shortest possibility.

count *\([^\)]\)(?=,)