Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION

Question

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION.

My answer/tried code is:

select city from station where REGEXP_LIKE(city,'[^aeiou]+');

But it doesn't seem to be correct.

Kindly help me with this.

Answer 1

As BackSlash have already commented, you've written the wrong REGEXP_LIKE pattern and you should change it to '^[aeiou].+', or you can even ommit .+ from your pattern, as you're only interested in the first letter of your string (containing more than 1 character):

select city from station where REGEXP_LIKE(city,'^[aeiou]');

Example with test data

Beware that would only return stations that start with lowercase vowels! If you also want to include uppercase vowels than add them to your pattern:

select city from station where REGEXP_LIKE(city,'^[aeiouAEIOU]');

or specify inside REGEXP_LIKE call that inputted pattern is case-insensitive with an 'i' flag, like this:

select city from station where REGEXP_LIKE(city,'^[aeiou]', 'i');

Example with test data

Kudos to MT0 for helpful comment!

I hope we helped!

Answer 2

use SUBSTR

select t.city from station t where lower(SUBSTR(city,1,1)) in ('a','e','i','o','u')