MySQL CONCAT() Returning NULL Even Though Values Exist

The MySQL CONCAT() function is used to concatenate two or more columns or strings together.

I had a scenario earlier today where, when concatenating a few columns together the outcome would always be NULL. An example of this query was as follows where I was concatenating a series of address fields into a single value:

1. SELECT   
2.     CONCAT(address_1, ' ', address_2, ' ', address_3, ' ', postcode) AS address  
3. FROM  
4.     my_table  

Even though I knew at least one of the address fields contained a valid value, the result would always be NULL. After not being able to find an obvious solution for myself I turned to the documentation where I would eventually obtain the reason.

THE CAUSE

In the MySQL documentation it states:

CONCAT() returns NULL if any argument is NULL.

My address fields could, and did, contain NULL values so this would seem to explain the issue I was experiencing.

THE SOLUTION

My initial thought when coming to solve the issue was to try and do some crazy IF on each field like so:

1. SELECT   
2.     CONCAT(  
3.         IF (ISNULL(address_1), '', address_1), ' ',   
4.         IF (ISNULL(address_2), '', address_2), ' ',   
5.         IF (ISNULL(address_3), '', address_3), ' ',   
6.         IF (ISNULL(postcode), '', postcode)  
7.     ) AS address  
8. FROM  
9.     my_table  

To be honest, I’m not sure if this would even work as fortunately I found a simpler solution short after. The solution that worked for me came in the form of a similar MySQL function called CONCAT_WS():

1. SELECT   
2.     CONCAT_WS(' ', address_1, address_2, address_3, postcode) AS address  
3. FROM  
4.     my_table  

As you can see, this is a much cleaner approach to the one I originally had planned and, unlike CONCAT(), CONCAT_WS() doesn’t return NULL if just one of the fields is NULL. The only difference between the final solution and the original query is the order of the parameters. Note how CONCAT_WS() expects the first parameter to be the separator?

Note: The final solution only works if the separator between each field will the same. If you plan on having different separators then the more advanced option proposed first might be the best option.