Friday, July 04, 2008

MySQL Find: concat_ws(...)

I'm working with a database table that contains city, state and country. The values are all nullable, so it's possible to have:

 Arlington
 Arlington - USA
 VA - USA
 Arlington - VA - USA

I thought it would be handy to add a column to the view that table which was the location the user was in. This location field would be properly formatted so my code wouldn't need to be filled with if(r.city) { ... } ....

Turns out, writing the code to format the location was really tricky. I ended up with a massive CASE statement and various IFNULL(...) clauses.

This morning, while browsing the docs for another reason I found: concat_ws (meaning: concat with separator).

It does exactly what I want. My SQL statement just turned into:

 SELECT ...
     concat_ws(', ', city, state, country) AS location
  ...

How cool is that? Every once in a while MySQL pleasantly surprises me.

No comments:

Post a Comment