SQL query to check if a name begins and ends with a vowel

Question

I want to query the list of CITY names from the table STATION(id, city, longitude, latitude) which have vowels as both their first and last characters. The result cannot contain duplicates.

For this is I wrote a query like WHERE NAME LIKE 'a%' that had 25 conditions, each vowel for every other vowel, which is quite unwieldy. Is there a better way to do it?

Answer 1

You could use a regular expression:

SELECT DISTINCT city FROM   station WHERE  city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$' 

Answer 2

in Microsoft SQL server you can achieve this from below query:

SELECT distinct City FROM STATION WHERE City LIKE '[AEIOU]%[AEIOU]' 

Or

SELECT distinct City FROM STATION WHERE City LIKE '[A,E,I,O,U]%[A,E,I,O,U]' 

Update --Added Oracle Query

--Way 1 --It should work in all Oracle versions

SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(LOWER(CITY), '^[aeiou]') and  REGEXP_LIKE(LOWER(CITY), '[aeiou]$'); 

--Way 2 --it may fail in some versions of Oracle

SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(LOWER(CITY), '^[aeiou].*[aeiou]'); 

--Way 3 --it may fail in some versions of Oracle

SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY, '^[aeiou].*[aeiou]', 'i');