Regarding mysql where clause with empty string

Question

My query

SELECT order_id, order_item_id, product_id, date_due, ship_via 
    FROM order_shipment 
    WHERE (product_id LIKE 'PP80403396-502%%' AND ship_via != '');

OR

SELECT order_id, order_item_id, product_id, date_due, ship_via 
    FROM order_shipment 
    WHERE (product_id LIKE 'PP80403396-502%%' AND trim(ship_via) != '');

does not give me any results.

My expectation would be to display to display the results where ship_via field is NULL or is not empty.

Null and empty string are not the same thing. Add a check for null too (or use `coalesce`/`isnull` to check both at once). — Bridge, Sep 24 '12 at 20:50
@MichaelKrelin-hacker https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce — Bridge, Sep 24 '12 at 20:51
@Bridge, thanks. Edited my answer ;-) I prefer `coalesce` over `ifnull`, but I didn't know `mysql` has one ;-) — Michael Krelin - hacker, Sep 24 '12 at 20:53
Your where clause should be `WHERE (product_id LIKE 'PP80403396-502%%' AND (trim(ship_via) != '' or ship_via is null));` — rs., Sep 24 '12 at 20:58
I was taking logic as following: I thought mysql would go through all the rows and skip over all the rows with empty strings and include the rest. Obviously there is a flaw. Thanks again for the heads up. — user1558426, Sep 24 '12 at 21:25

score 0 · Accepted Answer · answered Sep 24 '12 at 20:50

0

NULL is not empty string and not not empty string. And the result of comparison is neither true, nor false, it's NULL. You should use COALESCE to test for NULL or empty. Or OR.

answered Sep 24 '12 at 20:50

Michael Krelin - hacker

138,757
24
193
173

COALESCE will display first non-null result in the list. I need to display results where ship_via is null OR non-empty string. – user1558426 Sep 24 '12 at 21:08
`COALESCE(ship_via!='',TRUE)` ? – Michael Krelin - hacker Sep 25 '12 at 05:36

Regarding mysql where clause with empty string

1 Answers1