Sql query to check if a name begins and ends with a vowel

Pattern matching with regex

For more advanced pattern matching, we can employ regex in SQL databases like MySQL and PostgreSQL. We use a pattern '^[aeiouAEIOU].*[aeiouAEIOU]$' to confirm that a name starts and ends with a vowel:

SELECT DISTINCT city
FROM station
WHERE city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$'; -- "RegEx - is it of this world or the other?"


• Operator ^ denotes the start of the name.
• [aeiouAEIOU] matches any vowel (brace yourself, Consonants!).
• .* signifies any characters in between (the wild west!).
• The ending [aeiouAEIOU]$ asserts that it ends with a vowel.
• DISTINCT ensures unique results are returned (because nobody likes duplicates).

Oracle database case-insensitive matching

In Oracle, we can use REGEXP_LIKE with the 'i' flag for a case-insensitive search:

SELECT DISTINCT CITY
FROM STATION
WHERE REGEXP_LIKE(CITY, '^[aeiou].*[aeiou]$', 'i'); -- "i" here is not being egoistic, just case-insensitive.


In this scenario, the 'i' flag allows us to avoid listing both lower and upper case vowels separately, yielding a more compact query.

Non-regex based approach

If your SQL environment doesn't support regex, don't panic! You can use SUBSTRING and IN to achieve the same result:

SELECT DISTINCT city
FROM station
WHERE SUBSTRING(city, 1, 1) IN ('A', 'E', 'I', 'O', 'U', 'a', 'e', 'i', 'o', 'u')
AND SUBSTRING(city, -1, 1) IN ('A', 'E', 'I', 'O', 'U', 'a', 'e', 'i', 'o', 'u'); -- Playing IN and out here


Be mindful of the performance: Regex can lead to full table scans whereas using LIKE may allow better usage of indexes.

Consistent casing using LOWER

Where regex options like i aren't available, you can use the LOWER function to enforce case consistency:

SELECT *
FROM your_table
WHERE LOWER(name) LIKE 'a%' AND LOWER(name) LIKE '%a'; -- No case to worry with LOWER. Equality wins!


In the above query, every character in name gets converted to lower case before the pattern matching.

Choosing the right tool

Every database has specific features that can make your life easier. Try to use these to your advantage when deciding between LIKE, REGEXP, or other options. Each tool has its strengths and weaknesses; it's about picking the right one for the task at hand.