SQL query to check if a name begins and ends with a vowel
You could use a regular expression:
SELECT DISTINCT city
WHERE city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$'
Select Names of city starting and ending with vowels from STATION table
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
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
WHERE SUBSTR(CITY,1,1) NOT IN ('A','E','I','O','U')
AND SUBSTR(CITY,-1,1) NOT IN ('A','E','I','O','U');
-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
SELECT * from STATION where FIND_IN_SET(left(CITY,1),'A,E,I,O,U')>0;
select distinct CITY from STATION where substring(CITY,1,1) in