Concatenate with NULL values in SQL

Column1      Column2
-------      -------
 apple        juice
 water        melon
 banana
 red          berry

I have a table which has two columns. Column1 has a group of words and Column2 also has a group of words. I want to concatenate them with + operator without a space.

For instance: applejuice

The thing is, if there is a null value in the second column, i only want to have the first element as a result.

For instance: banana

Result
------
applejuice
watermelon
banana
redberry

However, when i use column1 + column2, it gives a NULL value if Comunm2 is NULL. I want to have “banana” as the result.

Answer

Use the COALESCE function to replace NULL values with an empty string.

SELECT Column1 + COALESCE(Column2, '') AS Result
    FROM YourTable

Advertisement

Answer