Concatenate with NULL values in SQL

Asked 2011-11-22T20:59:37.967

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.

Edit Report

2 Answers

The + sign for concatenation in TSQL will by default combine string + null to null as an unknown value.

You can do one of two things, you can change this variable for the session which controls what Sql should do with Nulls

http://msdn.microsoft.com/en-us/library/ms176056.aspx

Or you can Coalesce each column to an empty string before concatenating.

COALESCE(Column1, '')

http://msdn.microsoft.com/en-us/library/ms190349.aspx

answered 2011-11-22T21:04:51.880

You can do a union:

(SELECT Column1 + Column2 FROM Table1 WHERE Column2 is not NULL)
UNION
(SELECT Column1 FROM Table1 WHERE Column2 is NULL);

answered 2011-11-22T21:18:18.707

Concatenate with NULL values in SQL

2 Answers

Your Answer