When i write this query
SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id = 260;
return
title city
--------------- ------
Butterfly world Mohali
and when i write
SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id = '260abcxyz';
return
title city
--------------- ------
Butterfly world Mohali
see the second one is wrong. i want to get the value only IF ID EXACTLY MATCH ... how to solve this. thanks for your help.
You could convert the id
to a string so the comparison will be done exactly. You could use LIKE
to cause an implicit conversion
SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id LIKE '260abcxyz';
or alternative, you can perform the cast explicitly
SELECT cd.title, cd.city FROM coupon_detail cd WHERE CAST(cd.id AS CHAR) = '260abcxyz';
However, if all your IDs are integers, it's probably more appropriate to check these values before you try to query the database. (If the ID you're querying with isn't a number, there can't be a match anyway.)
This is happening because of Type Conversion in MySql. My sql treats '260abcxyz' as an integer in your query and AFAIK because first char is number MySql casts it to a number and it becomes only 260 and that's why you are getting result. If you write character first like 'abcxyz260' it will successes the comparison.
More explanation available here: Type Conversion in MySql
As a solution: you should take care that only numbers are passed in comparison and not combination of string and number.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With