CONCAT'ing NULL fields

Question

I have a table with three fields, FirstName, LastName and Email.

Here's some dummy data:

FirstName | LastName | Email Adam        West       [email protected] Joe         Schmoe     NULL 

Now, if I do:

SELECT CONCAT(FirstName, LastName, Email) as Vitals FROM MEMBERS 

Vitals for Joe is null, as there is a single null field. How do you overcome this behaviour? Also, is this the default behaviour in MS SQL Server?

Answer 1

Try

ISNULL(FirstName, '<BlankValue>') -- In SQL Server IFNULL(Firstname, '<BlankValue>') -- In MySQL 

So,

CONCAT(ISNULL(FirstName,''),ISNULL(LastName,''),ISNULL(Email,'')) -- In SQL Server CONCAT(IFNULL(FirstName,''),IFNULL(LastName,''),IFNULL(Email,'')) -- In MySQL 

would return the same thing without the null issue (and a blank string where nulls should be).

Answer 2

Look at CONCAT_WS

For example:

CONCAT_WS('',NULL,"TEST STRING","TEST STRING 2") 

Yields

TEST STRINGTEST STRING 2

This is easier than constructing IFNULL around everything. You can use an empty string as the separator.