Search code examples
sqldatabasestringnullconcatenation

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 Column2 is NULL. I want to have "banana" as the result.

Solution

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

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