CONCAT'ing NULL fields

concatenation, sql, sql-server

Solution

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).

Problem

I have a table with three fields, FirstName, LastName and Email. Here's some dummy data: ``` FirstName | LastName | Email Adam West adam@west.com 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?

Original source