CODEWITHSUNDEEP
sqlregexoracle-database
Pragyan
Pragyan

Reputation: 61

oracle regex match interval

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.

Upvotes: 0

Views: 222

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

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.

Upvotes: 3

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627082

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

Upvotes: 3

sticky bit
sticky bit

Reputation: 37472

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,}');

Upvotes: 0

Related Questions