Querying City Names Starting and Ending with Vowels Using Regular Expressions

Problem Background and Requirement Analysis

In practical database query scenarios, there is often a need to filter based on specific string patterns. The specific requirement discussed in this article is to query city names from the STATION table where both the first and last characters are vowels (a, e, i, o, u, case-insensitive), with no duplicate records in the result set.

Limitations of Traditional Approaches

Naive solutions might use multiple LIKE condition combinations, for example:

SELECT DISTINCT city FROM station 
WHERE city LIKE 'a%a' OR city LIKE 'a%e' OR city LIKE 'a%i' OR city LIKE 'a%o' OR city LIKE 'a%u'
OR city LIKE 'e%a' OR city LIKE 'e%e' OR city LIKE 'e%i' OR city LIKE 'e%o' OR city LIKE 'e%u'
-- 15 additional similar conditions omitted here

This approach requires 25 separate OR conditions (5 starting vowels × 5 ending vowels), resulting in verbose code that is difficult to maintain. When requirements change or the vowel set expands, modification efforts increase significantly.

Regular Expression Solution

In MySQL, the RLIKE operator with regular expressions provides a more elegant solution:

SELECT DISTINCT city
FROM station
WHERE city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$'

Regular Expression Pattern Analysis

Components of the pattern '^[aeiouAEIOU].*[aeiouAEIOU]$':

• ^: Matches the start of the string
• [aeiouAEIOU]: Matches any single vowel character (both uppercase and lowercase)
• .*: Matches zero or more of any character
• [aeiouAEIOU]: Matches any single vowel character again
• $: Matches the end of the string

Cross-Database Compatibility Considerations

Different database systems vary in their support for regular expressions. Here are implementations for several common databases:

Microsoft SQL Server Implementation

SQL Server uses extended LIKE pattern matching:

SELECT DISTINCT City FROM STATION WHERE City LIKE '[AEIOU]%[AEIOU]'

Note that SQL Server's LIKE pattern matching is case-insensitive by default, but character set definitions may affect matching results.

Oracle Database Implementation

Oracle provides the REGEXP_LIKE function with several viable approaches:

-- Method 1: Using two separate regex conditions
SELECT DISTINCT CITY FROM STATION 
WHERE REGEXP_LIKE(LOWER(CITY), '^[aeiou]') 
AND REGEXP_LIKE(LOWER(CITY), '[aeiou]$');

-- Method 2: Using single regex (may not be supported in some versions)
SELECT DISTINCT CITY FROM STATION 
WHERE REGEXP_LIKE(LOWER(CITY), '^[aeiou].*[aeiou]');

-- Method 3: Using case-insensitive flag
SELECT DISTINCT CITY FROM STATION 
WHERE REGEXP_LIKE(CITY, '^[aeiou].*[aeiou]', 'i');

Performance Analysis and Optimization Recommendations

Regular expression queries generally outperform combinations of multiple LIKE conditions, especially when processing large datasets. However, the specific performance of regex depends on:

• Optimization level of the database engine
• Complexity of the regex pattern
• Indexing status of the data table

For frequently executed queries, consider creating appropriate indexes on the city column. If performance remains a bottleneck, consider using function indexes (as in Oracle) or computed columns (as in SQL Server) to preprocess string matching conditions.

Practical Application Extensions

The patterns discussed in this article can be extended to more complex string matching scenarios, such as:

• Matching specific prefix and suffix combinations
• Validating email address formats
• Checking telephone number patterns
• Identifying product codes following specific encoding rules

Mastering the application of regular expressions in SQL can significantly enhance the flexibility and efficiency of data queries.