Tom R Tom R - 7 months ago 8
SQL Question

CONCAT'ing NULL fields

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?

Answer

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