Question: 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.
Solution I tried but failed (giving incorrect output):
SELECT DISTINCT city FROM station
WHERE NOT REGEXP_LIKE(city, '^(a|e|i|o|u).*(a|e|i|o|u)$','i');
Solution which worked:
SELECT DISTINCT city FROM station
WHERE REGEXP_LIKE(city, ^[^aeiou].*[^aeiou]$,'i');
Please can anyone explain why the first solution is failing.
>Solution :
The first version logically says to not find cities which begin and end with vowels. However, it leaves open the possibility for a city which starts with a vowel, but does not end with a vowel. It also admits cities which do not start with a vowel, but end with a vowel. The second version only allows cities which start and end with no vowel letters.
As a side note, the regex in your first version would better be written as:
SELECT DISTINCT city
FROM station
WHERE NOT REGEXP_LIKE(city, '^[aeiou].*[aeiou]$', 'i');
That is, use the character class in square brackets rather than an alternation of single letters (though your alternation was correct, just slightly awkward).