mysql: why comparing a ‘string’ to 0 gives true?

by

MySQL automatically casts a string to a number:

SELECT '1string' = 0 AS res; -- res = 0 (false)
SELECT '1string' = 1 AS res; -- res = 1 (true)
SELECT '0string' = 0 AS res; -- res = 1 (true)

and a string that does not begin with a number is evaluated as 0:

SELECT 'string' = 0 AS res;  -- res = 1 (true)

Of course, when we try to compare a string with another string there’s no conversion:

SELECT '0string' = 'string' AS res; -- res = 0 (false)

but we can force a conversion using, for example, a + operator:

SELECT '0string' + 0 = 'string' AS res; -- res = 1 (true)

last query returns TRUE because we ar summing a string ‘0string’ with a number 0, so the string has to be converted to a number, it becomes SELECT 0 + 0 = 'string' and then again the string ‘string’ is converted to a number before being compared to 0, and it then becomes SELECT 0 = 0 which is TRUE.

This will also work:

SELECT '1abc' + '2ef' AS total; -- total = 1+2 = 3

and will return the sum of the strings converted to numbers (1 + 2 in this case).

More Related Contents:

  1. How to convert a string to date in MySQL?
  2. How to find similar results and sort by similarity?
  3. How do I do a fuzzy match of company names in MYSQL with PHP for auto-complete?
  4. How do you extract a numerical value from a string in a MySQL query?
  5. SQL order string as number
  6. How to remove all non-alpha numeric characters from a string in MySQL?
  7. How to escape single quotes in MySQL
  8. MySQL – length() vs char_length()
  9. Compare dates in MySQL
  10. Capitalize first letter. MySQL
  11. Can MySQL replace multiple characters?
  12. MySQL compare DATE string with string from DATETIME field
  13. MySQL floating point comparison issues
  14. How to fix double-encoded UTF8 characters (in an utf-8 table)
  15. MySql – Way to update portion of a string?
  16. MySQL : how to remove double or more spaces from a string?
  17. Strip last two characters of a column in MySQL
  18. MySql Query Replace NULL with Empty String in Select
  19. Last index of a given substring in MySQL
  20. Library not loaded: libmysqlclient.16.dylib error when trying to run ‘rails server’ on OS X 10.6 with mysql2 gem
  21. INSERT INTO … SELECT FROM … ON DUPLICATE KEY UPDATE
  22. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  23. mysql update query with sub query
  24. How to use django models with foreign keys in different DBs?
  25. Continue SQL query even on errors in MySQL workbench
  26. How to generate a UUIDv4 in MySQL?
  27. How can “set timestamp” be a slow query?
  28. MySQL throws Incorrect string value error
  29. mysql create table dynamically
  30. Errno 121, duplicate key on write or update?

Leave a Comment