CONCAT() and NULL fields in SQL

Here’s something I’d just always taken for granted, that I could just CONCAT() a whole bunch of fields and SQL would just bow to my demands and give me whatever I wanted. Say something like:

CONCAT(
     t.billing_address1, '\n',
     t.billing_address2, '\n',
     t.city, ', ', t.state, ', ', t.zip, '\n',
     t.country
) AS address

and it would just work (where t, of course, is an alias for my table).

Well, it turns out that if any one of those fields is NULL, the whole thing will be NULL. When you pause to think about it, you want to say “Of course you freaking moron, anything operationed with NULL is NULL. How can you expect predictable output from unknown input?” but it’s a lot harder to think of as a solution when you are staring at data that just doesn’t make sense arbitrarily.

Here’s how that query needs to actually be written:

CONCAT(
     IF(ISNULL(t.billing_address1), '', t.billing_address1),
     IF(ISNULL(t.billing_address2), '', CONCAT('\n', t.billing_address2)),
     IF(ISNULL(t.city), '\n', CONCAT('\n', t.city)),
     IF(ISNULL(t.state), '', CONCAT(', ', t.state)),
     IF(ISNULL(t.zip), '', CONCAT(', ', t.zip)),
     IF(ISNULL(t.country), '', CONCAT('\n', t.country))
) AS address

And…happiness.