mysql - 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]$'