oracle regex match interval

Asked by At

I want to find out all the name which contains minimum 2 consecutive a and not more then 3 consecutive a.

I written the quires but It not fulfill the requirement.

Please look the below quires

SELECT FIRST_NAME,LAST_NAME
FROM EMPLOYEES
WHERE REGEXP_LIKE(FIRST_NAME,'a{2,3}')

Output: enter image description here

But I need only "Srikrushnaaa".

Thanks.

3 Answers

0
sticky bit On

AND a second regexp_like that checks for more than three 'a's.

SELECT FIRST_NAME,
       LAST_NAME
       FROM EMPLOYEES
       WHERE REGEXP_LIKE(FIRST_NAME,'a{2,3}')
             AND NOT REGEXP_LIKE(FIRST_NAME,'a{4,}');
3
Wiktor Stribi┼╝ew On

You may use

WHERE REGEXP_LIKE(FIRST_NAME,'([^a]|^)a{2,3}([^a]|$)')

Details

  • ([^a]|^) - any char but a or start of string
  • a{2,3} - 2 or 3 as
  • ([^a]|$) - any char but a or end of string

See the regex demo and the regex graph:

enter image description here

3
Gordon Linoff On

I would simply do:

WHERE FIRST_NAME LIKE '%aa%' AND
      FIRST_NAME NOT LIKE '%aaaa%'

Regular expressions don't seem to be needed for this.