'Select Names of city starting and ending with vowels from STATION table

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

I tried to run this query but it is returning an empty output

/*
Enter your query here.
*/
SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY LIKE '[aeiou]%[aeiou]';
mysqlsql


Solution 1:[1]

The LIKE pattern you are using is an extension only supported by SQL Server (and Sybase). In MySQL, you can use regular expressions:

WHERE CITY REGEXP '^[aeiou].*[aeiou]$'

Solution 2:[2]

you can use this:

select distinct city 
from station 
where city rlike '^[aeiou].*[aeiou]$';

Solution 3:[3]

select distinct city from station where city regexp '^[AEIOU].*[AEIOU]$';

Solution 4:[4]

Below query works in MS SQL SERVER-

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) IN ('a', 'e', 'i', 'o', 'u')
AND RIGHT(CITY,1) IN ('a', 'e', 'i', 'o', 'u');

Solution 5:[5]

SELECT DISTINCT CITY 
FROM STATION 
WHERE LOWER(LEFT(CITY, 1)) IN ('a', 'e', 'i', 'o', 'u') 
      AND LOWER(RIGHT(CITY, 1)) IN ('a', 'e', 'i', 'o', 'u');

Solution 6:[6]

select DISTINCT CITY from STATION where CITY like 'E%e' OR CITY like 'I%i' OR CITY like 'U%u' OR CITY like 'A%a' OR CITY like 'O%o' OR CITY like 'A%e' OR CITY like 'A%i' OR CITY like 'A%o' OR CITY like 'A%u' OR CITY like 'E%a' OR CITY like 'E%i' OR CITY like 'E%o' OR CITY like 'E%u' OR CITY like 'I%a' OR CITY like 'I%e' OR CITY like 'I%i' OR CITY like 'I%o' OR CITY like 'I%u' OR CITY like 'O%a' OR CITY like 'O%e' OR CITY like 'O%i' OR CITY like 'O%u' OR CITY like 'U%a' OR CITY like 'U%e' OR CITY like 'U%i' OR CITY like 'U%o';

QUITE LENGTHY BUT YOU WILL GET THE QUERY RIGHT

Solution 7:[7]

The simplest query to get desired results

SELECT DISTINCT CITY 
FROM STATION 
WHERE 
  LOWER(LEFT(CITY,1)) IN ('a', 'e', 'i', 'o', 'u') 
  AND LOWER(RIGHT(CITY,1)) IN ('a', 'e', 'i', 'o', 'u');

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gordon Linoff
Solution 2 PM 77-1
Solution 3 Syscall
Solution 4 Ihor Konovalenko
Solution 5 Peter Csala
Solution 6 jagadish
Solution 7 ejuhjav

Related Questions