HackerRank SQL — City Names That Do Not Start With Vowels

This one was a bit more interesting than the usual filter problems. The task was to get city names from a table called STATION that do not start with a vowel, meaning no city names starting with A, E, I, O or U. On top of that, no duplicate names in the result.

The DISTINCT keyword handles the duplicate part easily. The trickier bit was figuring out how to check the first letter of a city name. That is where SUBSTR comes in. SUBSTR lets you pick out a specific part of a text value. I used it to grab just the first character of the city name and then checked if it is not a vowel.

Here is what I wrote:

SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY, 1, 1) NOT IN ('A', 'E', 'I', 'O', 'U');

DISTINCT makes sure no city name shows up more than once. SUBSTR(CITY, 1, 1) pulls out just the first letter of the city name. NOT IN then checks that first letter against the list of vowels and if it matches any of them, that row gets filtered out.

This was the first problem where I had to think about the characters inside a value rather than just the value as a whole. SUBSTR is a handy little function and I can already see it coming up a lot in problems like this.

Once you understand what SUBSTR is doing, the rest of the query is pretty straightforward.