CODEWITHSUNDEEP
sqloracle-databaseregexp-like
camdixon
camdixon

Reputation: 882

SQL using regular expression REGEXP_LIKE()

I'm having some issues where I can't seem the like regex to match 3 or more a's or e's in the name.

Find all managers that manage employees with at least 3 letters 'a' or 'e' in their name (both uppercase and lowercase). For instance having 2 'a' and 1 'e' in the name, will satisfy the selection criteria

select manager_name
  from manages
 where regexp_like(employee_name, '[a,e]{3, }');

When I do this it shows a proper list with an 'e' or 'a' in it, but when I try to do 3 or more it returns blank set. Also sample data provided below.

select manager_name
  from manages
 where regexp_like(employee_name, '[a,e]');

Sample Data

William Gates III
Lakshmi Mittal
Ingvar Kamprad
Lawrence Ellison
Mark Zuckerberg
Sheryl Sandberg
Liliane Bettencourt
Michael Dell

Upvotes: 0

Views: 923

Answers (3)

David Faber
David Faber

Reputation: 12486

You might try using REGEXP_COUNT() instead of REGEXP_LIKE():

SELECT manager_name
  FROM manages
 WHERE REGEXP_COUNT(employee_name, '[ae]', 1, 'i') >= 3;

The value of the third parameter in REGEXP_COUNT() indicates the position of where the search should start (in this case, we want to start at the beginning) while the match_parameter 'i' indicates that the search should be case-insensitive.

REGEXP_COUNT() was added with Oracle 11g; if you're using an earlier edition of Oracle then you can try the following:

SELECT manager_name
  FROM manages
 WHERE REGEXP_INSTR(employee_name, '[ae]', 1, 3, 0, 'i') > 0;

The fourth parameter above (3) is the number of the occurrence of the regular expression.

or:

SELECT manager_name
  FROM manages
 WHERE REGEXP_SUBSTR(employee_name, '[ae]', 1, 3, 'i') IS NOT NULL;

Either way it makes the regular expression simpler than if you use REGEXP_LIKE().

Upvotes: 0

Enissay
Enissay

Reputation: 4953

You're looking for this instead

(.*[ae]){3,}

The .* accepts differents chars between those wanted

So your query becomes:

select manager_name
  from manages
  where 
  regexp_like(employee_name, '(.*[ae]){3,}', 'i');

The i flag is for insensitive match, so capital AE are taken into account to... If ommitted, sensitive match is performed...

You can also use simply {3} instead of {3,}, it will produce the same results in this case

Upvotes: 1

MT0
MT0

Reputation: 167867

If you want at least 3 a's or e's anywhere in the name then:

select manager_name
from   manages
where  regexp_like(employee_name, '(.*?[ae]){3,}', 'i' );

If you want at least 3 consecutive a's or es then:

select manager_name
from   manages
where  regexp_like(employee_name, '.*[ae]{3,}', 'i' );

Upvotes: 0

Related Questions