Reputation: 73
I am running a query on a column postal (type double)
.
SELECT * FROM `table` WHERE `postal` LIKE 'abcdef'; # returns 1 record
and the same query using = returns 100+ records.
SELECT * FROM `table` WHERE `postal` = 'abcdef'; # returns 107 record
What could be the reason?
Upvotes: 0
Views: 138
Reputation: 9042
The following things affects the result (not the complete list!)
LIKE
operatorIn each cases an implicit conversion occours: MySQL tries to convert the values to a common data type. In the first case case 'abcdef'
will be converted to double
which results to 0
. This is why you get 107 records when comparing with equals (=).
SELECT * FROM `table` WHERE `postal` = 'abcdef'; # returns 107 record
You should get exactly the same result by running
SELECT * FROM `table` WHERE `postal` = 0;
In MySQL, LIKE is permitted on numeric expressions. (This is an extension to the standard SQL LIKE.)
This means that SELECT CASE WHEN 10 LIKE '1%' THEN 1 ELSE 0 END
is allowed and results to 1 (matched)
To be honest, I'm not sure which double value could match with LIKE operator with the pattern 'abcdef'.
Upvotes: 0
Reputation: 234
You are using LIKE
on a DOUBLE
field, you should not do that.
LIKE
is reserved for pattern matching on strings. Use =
for numbers, or convert your digit to a string first using CONVERT
and then apply the logic with LIKE
.
Upvotes: 1
Reputation: 327
LIKE
will check and return similar values where as =
will check for the exact value.
Upvotes: 0