Mysql - Can you check for both a blank string and 0 in one condition?

Question

I want to check in mysql if a column is either blank, ie '', or 0. Is there a way to do this with one condition? Like

• WHERE order_id > ''

or

• WHERE order_id != ''

Would either of these work, or is there a different solution?

Answer 1

Does this qualify as one condition?

... WHERE order_id IN ('0', '');

Answer 2

why dont u use a smiple query where both of ur conditions are going to be tested

select * from tbl_name where order_id=' ' or order_id = 0

try this it will work

Answer 3

This is more a question of data quality. In a well designed database, there should be a fairly clear-cut difference between '' and 0.

If you're being vague about it, there are quite a lot of values that could be interpreted as "blank" in addition to these two. NULL is the obvious one, but what about white space? Or a string containing 0.00, or even if you're looking for a numeric value, any non-numeric string.

Ideally, the data should be stored in a format that matches the type of data it is supposed to hold - for example, if you're expecting a numeric field, it should be an int, or another numeric type, depending on exactly what you want to store. That way, it can never contain a string value, so you would never need to check for it.

If you can't change the type in the DB itself, the next best solution is to cast the value as that data type you are expecting in the select query. eg:

SELECT CAST(myfield as int) as myfieldnum FROM table where myfieldnum != 0

See the MySQL manual for more info: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

However, in the end, it does depend on exactly what you are expecting the data field to contain, and how you want to react to different types of content.

Answer 4

I experimented a bit and it seems the answer is:

• WHERE order_id != 0

This will show results where order_id is not 0 and also not blank