SQL using regular expression REGEXP_LIKE()

Question

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

Can you share some sample input and explain how the returned output is wrong. Also, which [tag:rdbms] are you using? — Mureinik, Feb 12 '16 at 14:00
Oracle, and it returns a blank set when it isn't supposed to. — camdixon, Feb 12 '16 at 14:02

Enissay · Accepted Answer · 2016-02-12T14:32:16.553

1

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

edited Feb 12 '16 at 14:32

answered Feb 12 '16 at 14:02

Enissay

4,969
3
29
56

what does the .* do? Any one character one or more times? – camdixon Feb 12 '16 at 14:03
What does the 'i' do at the end of that query? – camdixon Feb 12 '16 at 14:07
The `'i'` is the match parameter and tells Oracle to do a case-insensitive search. `.*` is any character (`.`) zero or more times (`*`). – David Faber Feb 12 '16 at 22:12

score 0 · Answer 2 · answered Feb 12 '16 at 14:24

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

score 0 · Answer 3 · answered Feb 12 '16 at 22:10

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().

SQL using regular expression REGEXP_LIKE()

3 Answers3