Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.
Input Format
The STATION table is described as follows:
FIELD TYPE
ID NUMBER
CITY VARCHAR2(21)
STATE VARCHAR2(2)
LAT_N NUMBER
LONG_W NUMBER
I write the below query but it does not work. Any suggestion?
SELECT DISTINCT (CITY)
FROM STATION
WHERE NOT regexp_like(lower(CITY),'^[aeiou].*')
AND regexp_like(lower(CITY),'.*[aeiou]$');
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