Oracle REGEXP confusion-CodePudding

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.

CodePudding user response：

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).

CodePudding user response：

Can you explain how I can fix my first code only for the required result, instead of using the second code, just out of curiosity.

In boolean logic (NOT A AND NOT B) = NOT (A OR B) so if is A is "starts with a vowel" and B is "ends with a vowel" then you want to find the values where it does not either start or end with a vowel:

Which could be:

SELECT DISTINCT city
FROM   station
WHERE  NOT (  REGEXP_LIKE(city, '^(a|e|i|o|u)','i')
           OR REGEXP_LIKE(city, '(a|e|i|o|u)$','i') );

or, as a single regular expression:

SELECT DISTINCT city
FROM   station
WHERE  NOT REGEXP_LIKE(city, '^(a|e|i|o|u)|(a|e|i|o|u)$','i');

Note: you can replace (a|e|i|o|u) with [aeiou].