0

I was practicing this question [Weather Observation Station 11] When I used

select distinct city
from station
where regexp_like(city, '^[^aeiou]|[^aeiou]$');

gave me the correct answer (458 results) where

select distinct city
from station
where not regexp_like(city, '^[aeiou]|[aeiou]$');

gave me the wrong answer (299 results).

I felt both '^[^aeiou]' and NOT '^[aeiou]' should gave me the not starting with aeiou results, but apprently not. What's the logical differences between these two answers?

Meruemu
  • 519
  • 1
  • 7
  • 26
  • @buddemat what about that? That gives you starting with vowel OR ending with vowel results. – Meruemu Nov 30 '20 at 19:59
  • 1
    Sorry, I should have written more explanation. My point was the same as in the answer below, just added the parentheses to point out that the second one is OR and negated becomes AND. I planned to write more but was on the bus and suddenly realised I needed to get off. Either way, since you got a good answer, I'll delete my comments to avoid confusion... – buddemat Nov 30 '20 at 20:40
  • What version are you running? I do not know of `regexp_like()`. – Rick James Nov 30 '20 at 21:48
  • @RickJames I was doing online practice on HackerRank. So probably not version related. regexp_like() is here https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-like. I learned it from other questions here too. – Meruemu Dec 01 '20 at 15:04
  • @Meruemu - That is new with 8.0. (Older versions did not have that as a function). – Rick James Dec 01 '20 at 15:58
  • I didn't know or use it before – Meruemu Dec 01 '20 at 16:20

1 Answers1

2

They appear same but they are different.

regexp_like(city, '^[^aeiou]|[^aeiou]$')

This will return a match if a city starts OR ends with a consonant (non-vowel).

However:

not regexp_like(city, '^[aeiou]|[aeiou]$')

will return a match if a city doesn't start and doesn't end with a vowel or in other words it will return a match if a city starts AND ends with a consonant (non-vowel).

Note different between OR and AND in both expressions which will cause more matches in first expression that second one.

anubhava
  • 664,788
  • 59
  • 469
  • 547