Query City Names Starting and Ending With Vowels

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

You could use a regular expression:

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

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

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

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

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;

Get city name either do not start with vowels and do not end with vowels

You may try this version:

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

The regex pattern here matches a whitelist of cities which do not start and do not end with a vowel. Here is an explanation of the regex pattern:

^              from the start of the city name
[^aeiouAEIOU]  match a single non vowel character (lowercase or uppercase)
.*             match any zero or more middle characters
[^aeiouAEIOU]  match another single non vowel character
$              end of the city name

Get city name either do not start with vowels or do not end with vowels

Assuming you are using MySQL, Here is what you are looking for

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

Footnote : RLIKE and DISTINCT

How to find the city name not starting and ending with a vowel

A little easier to read would be:

    SELECT DISTINCT  CITY 
    FROM STATION 
    WHERE  SUBSTR(CITY,1,1) NOT IN ('A','E','I','O','U')
    AND SUBSTR(CITY,-1,1) NOT IN ('A','E','I','O','U');

Note the -1 in the second WHERE clause which tells Oracle to search from the end of the string.

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

Another ways:

SELECT * from STATION where FIND_IN_SET(left(CITY,1),'A,E,I,O,U')>0;

And

select distinct CITY from STATION where substring(CITY,1,1) in
('A','E','I','O','U');