Could someone help me clean this up? (Beginner, SQL) [duplicate]

Question

I'm doing exercises (SQL beginner practice) on hackerrank.com, which I'm sure many of you are familiar with.

This is the 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.

My answer was the following:

SELECT DISTINCT(city)
FROM station
WHERE city NOT LIKE 'A%'
    AND city NOT LIKE 'E%'
    AND city NOT LIKE 'I%'
    AND city NOT LIKE 'O%'
    AND city NOT LIKE 'U%'
    AND city NOT LIKE '%a'
    AND city NOT LIKE '%e'
    AND city NOT LIKE '%i'
    AND city NOT LIKE '%o'
    AND city NOT LIKE '%u'

I know this is a ridiculous way to go about it- can you guys offer suggestions on how to make this shorter? I'm just not knowledgable enough in SQL at the moment to make it more succinct. If you could show a couple ways to go about it, that would be really helpful.

Answer 1

In MySQL, you can use the REGEXP operator:

select distinct city
from station
where city not regexp '^[aeiou].*[aeiou]$'

If your column has a case-sensitive collation, then you can lower the value before comparison:

where lower(city) not regexp '^[aeiou].*[aeiou]$'

Or, if you are running MySQL 8.0, you can use regexp_like() and set the match argument to 'i' to make the search case-insensitive:

where not regexp_like(city, '^[aeiou].*[aeiou]$', 'i')