LIKE '[charlist]%' does not work in MySQL (phpMyAdmin)

Question

There is a table called Students . I want to extract the names of students whose name begins with "n" or "p" or "y". I know that in TSQL (MS SQL server) I can write a 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 not able to get the correct result set. I tried to convert the letters of the student name to the same case that is mentioned in charlist. I did a little work on search engines and found out that in MySQL we need to specify this as a regular expression. I fulfilled the request below and got the expected result.

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

I want to know why the LIKE '[charlist]%' syntax LIKE '[charlist]%' does not work with MySQL. Is this due to the implementation in MySQL (MySQL does not support syntax) or is there something wrong with the version of phpmyadmin that I use?

Any help in this regard is much appreciated. Thanks:)

Answer 1

There is an even shorter way to write your request:

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

And if you are worried about case sensitivity:

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

In MySQL, matching a REGEXP pattern successfully executes anywhere in a value that is different from LIKE where the pattern must match the entire value.

The following link will give you a more complete answer:

MySQL Pattern Matching

Answer 2

MySQL: Case Insensitive: SELECT * FROM Students WHERE StudentName RLIKE '^[npy]' ; SELECT * FROM Students WHERE StudentName CAST(RLIKE as BINARY) '^[npy]' ; sensitive: SELECT * FROM Students WHERE StudentName CAST(RLIKE as BINARY) '^[npy]' ;