Concatenate with NULL values in SQL

Question

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 1

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

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

Answer 2

Standard SQL requires that string concatenation involving a NULL generates a NULL output, but that is written using the || operation:

SELECT a || b   FROM SomeTable; 

The output will be null if either a or b or both contains a NULL.

Using + to concatenate strings indicates that you are using a DBMS-specific extension. The behaviour might be the same as the standard requires - indeed, that seems to be the gist of your question.

Some DBMS - notably Oracle - tend to treat null strings as equivalent to empty strings; then you can concatenate away merrily. However, that behaviour is not strictly standard-compliant if the || operator is used.

Consider using COALESCE or NVL or IFNULL or some similar function to map the NULL to an empty string before concatenating.