CODEWITHSUNDEEP
mysqlstringcomparison
DiegoDD
DiegoDD

Reputation: 1665

mysql: why comparing a 'string' to 0 gives true?

I was doing some MySQL test queries, and realized that comparing a string column with 0 (as a number) gives TRUE!

select 'string' = 0 as res; -- res = 1 (true), UNexpected! why!??!?!

however, comparing it to any other number, positive or negative, integer or decimal, gives false as expected (of course unless the string is the representation of the number as string)

select 'string' = -12 as res; -- res = 0 (false), expected
select 'string' = 3131.7 as res; -- res = 0 (false), expected
select '-12' = -12 as res; -- res = 1 (true), expected

Of course comparing the string with '0' as string, gives false, as expected.

select 'string' = '0' as res; -- res = 0 (false), expected

but why does it give true for 'string' = 0 ?

why is that?

Upvotes: 21

Views: 10638

Answers (3)

Cristhian Murcia
Cristhian Murcia

Reputation: 46

if you want to fix it you can compare two strings:

select 'string' = convert(0,char) as res --- res = 0

Upvotes: 0

fthiella
fthiella

Reputation: 49049

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).

Upvotes: 29

Russell Zahniser
Russell Zahniser

Reputation: 16364

"Strings are automatically converted to numbers and numbers to strings as necessary." This means that in order to compare a string to a number, it tries to parse a number from the start of the string. In this case there is no number there, so it converts to 0, and 0 = 0 is true.

Upvotes: 2

Related Questions