[FIXED] How to write an SQL query to match city names ending with vowels?

Issue

Why is it not working?

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. our result cannot contain duplicates.

SELECT DISTINCT CITY FROM STATION 
WHERE CITY LIKE '%A' OR CITY LIKE '%E' OR LIKE '%I' LIKE '%O' OR LIKE '%U' 
ORDER BY CITY;

What would be the correct query?

Solution

You query has syntax error, for each "or", you need to put city like instead of or. In addition, remember that in most of the dbms, it is case sensitive. If you want to ignore both upper case and lower case, do it like this. I am using mysql syntax, different dbms has different functions for lcase

SELECT DISTINCT CITY FROM STATION
WHERE lcase(CITY) LIKE '%a'
OR lcase(CITY) LIKE '%e'
OR lcase(CITY) LIKE '%i'
OR lcase(CITY) LIKE '%o'
OR lcase(CITY) LIKE '%u'
ORDER BY CITY;

Answered By - buqing

Answer Checked By - Marie Seifert (PHPFixing Admin)