CODEWITHSUNDEEP
mysqlsqlif-statementsubstring
AUTxRemoteC
AUTxRemoteC

Reputation: 23

mySQL: Why is if(condition,then,'') != '' not the same as if(condition,then,0) != 0

I just encountered something I can't explain when doing some queries on my mySQL database:

SELECT
if(LENGTH(partner_id) > 38, SUBSTRING(partner_id FROM 8), 0) as sub_id
from people
Where
if(LENGTH(partner_id) > 38, SUBSTRING(partner_id FROM 8), 0) != 0

That's nothing special so far, I only want to display a part of some partner_ids. But if I do the same statement slightly different (exhange the 0 with empty string ''): 

SELECT
if(LENGTH(partner_id) > 38, SUBSTRING(partner_id FROM 8), '') as sub_id
from people
Where
if(LENGTH(partner_id) > 38, SUBSTRING(partner_id FROM 8), '') != ''

I get way more results. Only difference: I put an empty string in the else-statement and compare the empty string. Is this some strange behaviour of the SUBSTRING function? Or do I miss the wood for the trees?

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

One explanation is that you have some partner ids where the substring starts with a 0.

In the first case, the query is doing an integer comparison. A string like '0a' evaluates to "0" as an integer. This is equal to 0, so they are filtered out.

In the second case, the query is doing a string comparison. A string like '0a' is different from '', so these pass the filtering clause.

Upvotes: 1

Related Questions