1

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'.

cise
  • 69
  • 1
  • 8
  • You can combine your current attempt with one of the solutions at https://stackoverflow.com/questions/8169471/how-to-count-the-number-of-occurrences-of-a-character-in-an-oracle-varchar-value – EdmCoff Jan 23 '20 at 19:45
  • thanks ended up using that and my solution was: ```select NAME from CLASS where NAME LIKE '%s' and REGEXP_COUNT(NAME, 'a') > 2;``` – cise Jan 23 '20 at 19:48
  • @cise Surely `'a.*a.*s$'` would so it, if you really want a regex? But Luke Winward's `like ''%a%a%a%s'` is simpler and faster. – William Robertson Jan 24 '20 at 23:10

4 Answers4

3

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.

Luke Woodward
  • 63,336
  • 16
  • 89
  • 104
1

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'
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
1

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

cise
  • 69
  • 1
  • 8
0

Try this:

select NAME from test where regexp_like(NAME,'[a]{2}[a-z]*[s]$');
Naveen Kumar
  • 1,988
  • 1
  • 7
  • 11