MySQL COALESCE function
Posted by Dan on 20 Dec 2012 @ 7:21 AMThis morning I needed to pull out names and email addresses from a MySQL table to import into a mailing list system.
The table has two name fields though - one a username, which is always populated, one a 'real' name, which is optional for the user. Since we want to use the subscriber's name in the emails we'll be sending I wanted to select the real name is if was populated, otherwise fall back to the username.
The MySQL COALESCE function does just this - pass it a list of fields and it will return the first non-NULL value in that list:
SELECT COALESCE(real_name, user_name) AS name FROM table;
This was new to me, and a very useful addition to the arsenal - previously I'd have used an IF statement to detect whether real_name has a LENGTH greater than 0, or post-processed the query results.