CODEWITHSUNDEEP
mysqlsql
Ollie
Ollie

Reputation: 337

SQL Strings that begin and end with Vowels - Looking for Shortcut

I have a newbie question regarding SQL. Is it possible to query a column in a table for strings that begin and end with vowels. I understand I can do the following....

    SELECT ColumnName
    FROM Table
    WHERE ColumnName LIKE 'A%a' or 'E%a' or 'I%a' or 'O%a' or 'U%a' or
    'A%e' or 'E%e' or 'I%e' or 'O%e' or 'U%e'...........etc...........;

But it feels like there has to be a shortcut. I just don't know what it is. Thank you!!

Upvotes: 1

Views: 1702

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

One approach which is a bit more aethestically pleasing than your original query which does not use REGEXP would be to use WHERE IN along with SUBSTRING():

SELECT ColumnName
FROM Table
WHERE LOWER(SUBSTRING(ColumnName, 1, 1)) IN ('a', 'e', 'i', 'o', 'u') AND
      LOWER(SUBSTRING(ColumnName, CHAR_LENGTH(ColumnName), 1)) IN ('a', 'e', 'i', 'o', 'u')

Upvotes: 4

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use REGEXP.

SELECT ColumnName
FROM Table
WHERE ColumnName REGEXP '^[aeiouAEIOU].*[aeiouAEIOU]$'

Upvotes: 2

Related Questions