I’m having trouble with a MySQL query that should match names starting with vowels. When I run this query:
SELECT *
FROM users
WHERE username LIKE '[aeiou]%';
It returns no results even though my table has plenty of usernames that start with vowels like ‘alice’, ‘edward’, ‘olivia’, etc. But when I try a simple pattern like this:
WHERE username LIKE 'A%';
It works perfectly and shows all usernames starting with ‘A’. I’m confused why the bracket notation isn’t working. I expected it to match any username beginning with a, e, i, o, or u. What am I doing wrong here? Is there a different syntax I should be using for this type of pattern matching in MySQL?
yeah, this tripped me up when i moved from mssql too. mysql’s like operator doesn’t recognize those brackets - it literally searches for the bracket characters. use regexp instead: username regexp '^[aeiou]'. just remember it’s case sensitive, so you’ll probably need to include uppercase letters.
MySQL doesn’t support bracket notation in LIKE patterns - those square brackets get treated as literal characters, not pattern matchers. I hit this same issue migrating from SQL Server to MySQL a few years back. Use REGEXP instead: SELECT * FROM users WHERE username REGEXP '^[aeiou]'; The caret anchors to the start of the string, and REGEXP actually supports character classes with brackets. You could chain multiple LIKE conditions with OR, but REGEXP’s way cleaner here.
Those square brackets are SQL Server syntax, not MySQL. MySQL’s LIKE operator treats them as literal characters, so you’re searching for usernames that actually start with “[aeiou]” - which probably don’t exist in your database. You’ve got two options: use multiple OR conditions like WHERE username LIKE 'a%' OR username LIKE 'e%' OR username LIKE 'i%' OR username LIKE 'o%' OR username LIKE 'u%', or go with regex: WHERE username REGEXP '^[aeiouAEIOU]'. The regex approach is cleaner and handles case-insensitive matching if you need it.