LIKE '[charlist]%' syntax not working in MySQL (phpMyAdmin)

Question

There is table named Students. I want to extract the names of students whose name starts with either 'n' or 'p' or 'y'. I know that in TSQL (MS SQL server) I can write the query as follows and it works:

SELECT * FROM Students WHERE StudentName LIKE '[npy]%'

But when I execute the same query in MySQL (phpmyadmin) I was unable to retrieve the correct result set. I tried converting the letters of the student name into the same case which is mentioned in the charlist. I did bit of googling and found out that in MySQL we need to specify this as a regular expression. I executed the below query and got the expected result.

SELECT * FROM Students WHERE StudentName REGEXP '[[:<:]]n | [[:<:]]p | [[:<:]]y'

I want to know the reason why LIKE '[charlist]%' syntax is not working with MySQL. Is it due to the implementation in MySQL (MySQL doesn't support the syntax) or something wrong with the phpmyadmin version I'm using?

Any help regarding this is highly appreciated. Thanks :)

Answer 1

There is an even shorter way to write your query:

SELECT * FROM Students WHERE StudentName REGEXP '^[npy]'

And if you're concerned about being case sensitive:

SELECT * FROM Students WHERE StudentName REGEXP BINARY '^[npy]'

In MySQL, a REGEXP pattern match succeeds anywhere in the value, which differs from LIKE where the pattern must match the entire value.

The following link will give you a more complete answer:

MySQL Pattern Matching