CODEWITHSUNDEEP
mysqlsqlselect
TSR
TSR

Reputation: 20592

How to concatenate two columns that might contain NULL values in a select statement?

I tried with:

SELECT CONCAT(col1, col2) AS NewCol FROM tableName

It works only if col1 and col2 are not null, else NewCol becomes null, even if one of col1 and col2 is not null. I want it to display whatever value that is available.

Upvotes: 1

Views: 802

Answers (3)

Forklift
Forklift

Reputation: 969

You can also use 

SELECT ISNULL([col1],'') + ISNULL([col2],'') AS NewCol FROM tableName

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can use CONCAT_WS. its NULL safe. the first argument is the separator.

sample

MariaDB [l]> SELECT CONCAT_WS('',NULL,'123');
+--------------------------+
| CONCAT_WS('',NULL,'123') |
+--------------------------+
| 123                      |
+--------------------------+
1 row in set (0.00 sec)

MariaDB [l]>

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use CONCAT_WS.

SELECT CONCAT_WS('', col1, col2) AS NewCol FROM tableName

It would return an empty string in case both the values are null.

Documentation:

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

Upvotes: 1

Related Questions