MySQL regular expression - not starting nor ending with vowels

Question

I was playing around with this challenge here: Weather Observation Station 12

And I tried submitting this answer:

SELECT DISTINCT CITY FROM STATION
WHERE CITY NOT REGEXP '^[aeiouAEIOU].*[aeiouAEIOU]$'

I know that this answer works (inspired by this very similar question):

SELECT DISTINCT CITY FROM STATION
WHERE CITY NOT RLIKE '^[aeiouAEIOU]' AND CITY NOT RLIKE '[aeiouAEIOU]$'

... But I couldn't write an accepted answer in a single regular expression. Can anyone explain why?

---

What it searches in

As a commenter pointed out, then above-written page requires login. So for good measures sake, I'll add here that it searches amongst 500 city-names, such as (each in their own row in the database):

Kissee Mills, Loma Mar, Sandy Hook, Tipton, Arlington, Turner, Slidell, Negreet, Glencoe, Chelsea, Chignik Lagoon, Pelahatchie, Hanna City, Dorrance, Albany, Monument, Manchester, Prescott, Graettinger, Cahone, Sturgis, Upperco, Highwood, Waipahu, Bowdon, Tyler, Watkins, Republic, Millville, Aguanga, Bowdon Junction, Morenci, South El Monte 

---

Differences between regular expressions

And I've heard that there are some minor differences in regular expressions from one language to another (PHP, JavaScript, Perl, MySQL, Ruby, etc.). But I can't find a page that explains what those differences are. I could sit down and read the documentation for each language and cross-reference it, - but are there not a place where it has been summed up?

Addition: I come from the PHP-world, - and every time I write a regular expression in MySQL, there's a seed of doubt thinking: 'Is this one of those things that are different?'. Ideally I was looking for a place, where I could kill that doubt. Especially because it's so difficult to Google, because regular expressions are so punctuation-heavy.

Answer 1

Use the [^aeiou] character class to represent a non vowel character:

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[^aeiou].*[^aeiou]$';

I assume that a city name would always be at least two characters. Note that REGEXP is not case sensitive.

Answer 2

The Weather Observation Station 12 question asks for city which don't start with a vowel AND don't end with a vowel. By writing in single regular expression like SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[aeiouAEIOU].*[aeiouAEIOU]$'

cases for cities like 'Alabama' would be handled but it would still output cities like 'Lee' or 'Charlotte' which either start OR end with a vowel.

This is the reason it has to be explicitly written like SELECT DISTINCT CITY FROM STATION WHERE CITY NOT RLIKE '^[aeiouAEIOU]' AND CITY NOT RLIKE '[aeiouAEIOU]$'

Also FYI, Weather Observation Station 11 problem accepts the single regular expression because it asks for OR.

Answer 3

select distinct city from station where not city regexp '^[aeiou].*[aeiou]$'; This is the correct solution to that hackerrank weather problem 11.

Problem 12 asks for AND while problem 11 asks for OR.

Please try both of them.

Answer 4

Let's have a really easy to understand solution of this problem We are just using the lower left and lower right operators to get hold of first and last chars and after which we are checking with not in operators to guide us for getting consonants outputs there!!!

SELECT DISTINCT CITY
FROM STATION 
WHERE LOWER(RIGHT(CITY,1))
NOT IN ('a','e','i','o','u')
AND LOWER(LEFT(CITY,1))
NOT IN ('a','e','i','o','u');

Answer 5

For question 11, it asks for stations either not starting with vowels or do not end with vowels. Here is my accepted solution:

SELECT DISTINCT(CITY) FROM STATION WHERE CITY REGEXP '^[^aeiou].*|[^aeiou]$';