How does string truthiness work in MySQL?

Question

I wanted to look for records where a certain string field was not blank or null, so I simply wrote SELECT ... FROM myTable WHERE x, assuming that blank and null strings would evaluate to false, but that doesn't appear to be the case.

The string "02306" is true, whereas "N06097EIP" is somehow false.

What's going on?

Edit: I'm aware of the workarounds, I simply want to know how the casting works.

Answer 1

In these expression string are first converted to numbers. "02306" is converted to 2306 which is >0 and therefore considered true, while "N06097EIP" (starting with non-digit) is converted to 0, which is evaluated as false.

Compare results of:

select convert("N06097EIP",signed)

and

select convert("02306",signed)

Answer 2

In a boolean context, such as

WHERE x

the expression x will be evaluated as an integer value. Note that MySQL considers a BOOLEAN as a numeric type.

http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html

It doesn't matter what type the expression x is; it's either an INTEGER type, or it will be converted to an INTEGER type, according to the documented conversion rules.

The end result is that the expression x will be evaluated to be either NULL, integer zero, or integer non-zero.

And those correspond to the boolean "truthiness" values of NULL, FALSE and TRUE.

The reason '02306' is considered TRUE is because this string converts to integer value 2306, which is non-zero.

The reason 'N06097EIP' is considered FALSE is because this string converts to integer value 0.

To can run a simple test case to verify:

SELECT IF( 0 = 'N06097EIP', 'is zero', 'is not zero')
SELECT 0 = 'N06097EIP'

The behavior you observe is entirely expected. It's all pretty straightforward. You may have been caught unawares, because the normative pattern is for us to avoid this type of evaluation and to instead use "workarounds" (as you put it) to return a boolean.