views:

40

answers:

4

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?

+2  A: 

Does this qualify as one condition?

... WHERE order_id IN ('0', '');
Daniel Vassallo
yes, that is one condition, but is it as optimized as just WHERE order_id = 0 (because that works, I tried it, it brings up blanks too). Or does it not matter...
esther h
A: 

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

Prateek
+3  A: 

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.

Spudley
yes, you are right, ideally all columns should hold only one type. But I didn't design this and I can't change it...
esther h
@esther: okay, so take the 'next best' approach, and cast it in the query.
Spudley
can you give me an example? would it be... WHERE CAST(order_id AS int) != 0 ??
esther h
I've edited my answer to include an example.
Spudley
of course, this only applies if the contents are going to be an integer, and you need to treat it as an integer.
Spudley
ok, i see, thanks, great answer
esther h
+1  A: 

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

esther h