Tom R Tom R - 2 years ago 84
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
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 Source


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


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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download