CODEWITHSUNDEEP
mysqlsqlwildcardsql-like
tdc
tdc

Reputation: 5464

MySQL LIKE pattern matching returning empty set when it should be returning values

I have a table structured like below (real data replaced with dummy data):

tableName 
+-----------+----------------+
| code      | description    |
+-----------+----------------+
| A         | text here      |
| b         | blah blah      |
| c         | sdfsdfsdfsdf   |
| d         | sdfsfkaljdaklj |
| e         | asdkjasdlkjasd |
| f         | adskljalkdj    |
| g         | asdjalkd       |
| h         | askdjavsd      |
+-----------+----------------+

I am trying to do the following command:

SELECT * FROM tableName WHERE description LIKE '__[aeiou]%';

I expect this select statement to return all results from the table where the third character in the description is a, e, i, o, or u.

However, I am getting 0 results for the about query. I know for a fact that valid results which match this pattern (3rd character is a, e, i, o or u) exists in the table.

What is wrong with my query?

Upvotes: 3

Views: 1057

Answers (2)

epdittmer
epdittmer

Reputation: 498

In MySQL the '_' character is a not part of the extended regular expression class and can not be used as such. use RLIKE to use regular expressions

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Use a regular expression instead of a SQL Server style LIKE pattern:

SELECT *
FROM tableName
WHERE description REGEXP '^..[aeiou].*$';

EDIT:

For those who don't read documentation thoroughly, the documentation says:

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

The following list describes some characteristics of extended regular expressions:

The list of features, such as [ and ] is in this list. The standard for LIKE has only two wildcards, % and _.

Upvotes: 4

Related Questions