CODEWITHSUNDEEP
sqloracle-database
cise
cise

Reputation: 85

Find the names of employees whose name has more than two ‘a’ in it and ends with ‘s’. ORACLE SQL

I'm attempting to select from a table the names of employees whose name has two 'a' in it and end with s. Heres what I have so far select NAME from CLASS where NAME LIKE '%s'

I know how to find names where they end with s but not sure how to search for names having atleast two 'a'.

Upvotes: 1

Views: 2643

Answers (4)

Naveen Kumar
Naveen Kumar

Reputation: 2006

Try this:

select NAME from test where regexp_like(NAME,'[a]{2}[a-z]*[s]$');

Upvotes: 0

Luke Woodward
Luke Woodward

Reputation: 64969

Am I missing something, or could you just not just write 

select NAME from CLASS where LOWER(NAME) LIKE '%a%a%a%s'

?

This selects every name that has at least three (i.e. more than two) as, and ends with an s.

Upvotes: 3

cise
cise

Reputation: 85

Found a solution: select NAME from CLASS where NAME LIKE '%s' and REGEXP_COUNT(NAME, 'a') > 2;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142798

One option might be

where regexp_count(name, 'a', 1, 'i') = 2
  and substr(lower(name), -1) = 's'
  • number of 'a' letters - starting at position 1, performing case insensitive search ('i') = 2
  • the last character is 's'

Upvotes: 1

Related Questions