Introduction

The Weather Observation Station 11 problem from HackerRank tests your ability to use SQL's pattern matching operators to filter city names based on whether they neither start nor end with a vowel. This is an interesting challenge to apply regular expressions (RLIKE) and LIKE operators to perform complex string manipulations.

Problem Statement

Query the STATION table to retrieve all distinct cities whose names do not start or end with a vowel. The vowels are a, e, i, o, u (lowercase). The task requires filtering cities whose first and last characters are both non-vowel characters.

SQL Query Solution

Using RLIKE:


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

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%' 
) 
OR NOT (
    CITY LIKE '%a' OR 
    CITY LIKE '%e' OR 
    CITY LIKE '%i' OR 
    CITY LIKE '%o' OR 
    CITY LIKE '%u'
);

Explanation

In the first query, we use the RLIKE operator, which supports regular expressions in MySQL. The regular expression '^[AEIOU].*[AEIOU]$' matches cities that both start and end with vowels. The NOT operator negates this, thus selecting cities whose names do not start and end with vowels.

The second query uses multiple LIKE conditions, where the NOT is applied to filter out cities that begin or end with any vowel. The first NOT condition excludes cities starting with vowels, and the second NOT condition ensures cities do not end with vowels. The combination ensures that only cities that neither start nor end with vowels are selected.

Note: In both queries, DISTINCT ensures that each city appears only once in the output, even if it appears multiple times in the table.

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 provided by the LIKE and RLIKE operators.

- The RLIKE operator uses a regular expression, which in the worst case might perform a linear scan of the string. - The LIKE operator performs simple pattern matching.

The space complexity is O(n) for storing the distinct cities in the result set, where n is the number of distinct cities that match the criteria.

Conclusion

This problem is an excellent exercise for practicing SQL's string manipulation capabilities. The use of the RLIKE operator in combination with regular expressions allows for powerful and flexible pattern matching. On the other hand, using LIKE in a more explicit manner demonstrates the power of basic string pattern matching in SQL. Understanding both approaches helps build a strong foundation for solving more advanced SQL string manipulation problems.

Related Problems for Practice