Introduction

The Weather Observation Station 12 problem from HackerRank challenges you to filter cities from the STATION table, retrieving distinct cities that do not start or end with vowels. This problem requires you to apply SQL pattern matching operators, such as LIKE and RLIKE, to effectively solve the task.

Problem Statement

Query the STATION table to retrieve all distinct cities whose names neither start nor end with a vowel. The vowels considered here are a, e, i, o, u (lowercase). The task is to identify cities whose names don't start or end with a vowel, by using SQL string pattern matching techniques.

SQL Query Solution

Using LIKE:


SELECT DISTINCT CITY
FROM STATION 
WHERE NOT (
    CITY LIKE 'A%' OR 
    CITY LIKE 'E%' OR 
    CITY LIKE 'I%' OR 
    CITY LIKE 'O%' OR 
    CITY LIKE 'U%' 
) 
AND NOT (
    CITY LIKE '%a' OR 
    CITY LIKE '%e' OR 
    CITY LIKE '%i' OR 
    CITY LIKE '%o' OR 
    CITY LIKE '%u'
);

Using RLIKE:


SELECT DISTINCT CITY
FROM STATION
WHERE CITY RLIKE '^[^AEIOU].*[^AEIOU]$';

Explanation

In the first query, the LIKE operator is used to filter cities based on whether they start or end with a vowel. The NOT operator negates this condition, ensuring that only cities whose names neither start nor end with vowels are returned. The query is structured with two NOT conditions:

  • The first condition checks if the city name starts with a vowel.
  • The second condition ensures the city name does not end with a vowel.

The second query uses the RLIKE operator, which allows us to use regular expressions for more powerful pattern matching. The regular expression '^[^AEIOU].*[^AEIOU]$' matches cities that:

  • Start with a non-vowel character (the [^AEIOU] part),
  • End with a non-vowel character (the [^AEIOU] part at the end).

The ^ indicates the beginning of the string, .* means any characters in between, and the $ denotes the end of the string.

Example Test Cases

Test Case 1

Input Table:

CITY
---------
Oslo
Madrid
Berlin
Chicago
Kano

Expected Output:


Madrid
Chicago

Explanation: Only Madrid and Chicago do not start or end with vowels.

Test Case 2

Input Table:

CITY
---------
Rome
Madrid
KualaLumpur
Doha
Zurich

Expected Output:


Madrid
KualaLumpur
Zurich

Explanation: These cities do not start and end with vowels.

Complexity Analysis

The time complexity of this query is O(n), where n is the number of rows in the STATION table. This is because each city name is checked against the conditions specified by the LIKE and RLIKE operators.

- The RLIKE operator utilizes regular expressions, which in the worst case may scan the string linearly. - The LIKE operator performs basic pattern matching, which is usually fast for simple patterns.

The space complexity is O(n) because we store the distinct cities in the result set, where n is the number of distinct cities that meet the criteria.

Conclusion

In this problem, we applied SQL’s powerful string matching capabilities to solve a real-world problem of filtering cities based on patterns. The LIKE operator offers a simple yet effective way to match city names based on prefix and suffix patterns, while the RLIKE operator allows for more flexible and complex matching using regular expressions.

Both approaches solve the problem efficiently, and understanding when to use each operator is a key takeaway. The LIKE operator is suitable for simpler cases, while RLIKE becomes invaluable when dealing with more complex patterns.

Related Problems for Practice