CODEWITHSUNDEEP
sqlmysql
John M
John M

Reputation: 14668

MYSQL - SELECT query with LIKE using %% returns incorrect value

Query:

SELECT * FROM table
  WHERE fieldA LIKE '%%' 
  AND fieldB LIKE '%%' 
  AND fieldC LIKE '%%'

This returns only records that have all fields completed. My thought would be that it should return all records in the table.

Does the '%%' actually represent that a value is needed?

UPDATE1:

Thanks to some good questions the solution was found:

Query should be like:

SELECT * FROM table
  WHERE if(fieldA IS NOT NULL,fieldA LIKE '%%',fieldA IS NULL)
  ...

Upvotes: 1

Views: 2371

Answers (1)

Matthew Jones
Matthew Jones

Reputation: 26190

LIKE '%%' matches any string, even ones with zero length. The result of your query is it is returning all rows where the three fields each have a string in them.

My guess is that the fields that are not completed are NULL. Maybe you should be checking for IS NOT NULL instead of LIKE '%%'?

Upvotes: 10

Related Questions