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?

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

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.

In mysql isnull wont work some time. try IFNULL(),

CONCAT(IFNULL(FirstName,''),IFNULL(LastName,''),IFNULL(Email,''))

SELECT ISNULL(FirstName,'')+ISNULL(LastName,'')+ISNULL(Email,'') as Vitals FROM MEMBERS

is recommended, but if you are really hooked on CONCAT, wrap it in {fn } and you can use the ODBC function like:

SELECT {fn CONCAT(ISNULL(FirstName,''), ISNULL(LastName,''), ISNULL(Email,''))} as Vitals FROM MEMBERS

If you need first<space>last but just last when first is null you can do this:

ISNULL(FirstName+' ','') + ISNULL(LastName,'')

I added the space on firstname which might be null -- that would mean the space would only survive if FirstName had a value.

To put them all together with a space between each:

RTRIM(ISNULL(Firstname+' ','') + ISNULL(LastName+' ','') + ISNULL(Email,''))

You can always use the CONCAT_NULL_YIELDS_NULL setting..

just run SET CONCAT_NULL_YIELDS_NULL OFF and then all null concatenations will result in text and not null..

If you get (like I do in MySQL):

#1582 - Incorrect parameter count in the call to native function 'ISNULL'

You can replace ISNULL function by COALESCE:

CONCAT(COALESCE(FirstName,''),COALESCE(LastName,''),COALESCE(Email,''))

Stefan's answer is correct. To probe a little bit deeper you need to know that NULL is not the same as Nothing. Null represents the absence of a value, or in other words, not defined. Nothing represents an empty string which IS in fact a value.

Undefined + anything = undefined

Good database tidbit to hold onto!

Starting from MS SQL Server 2012 it was introduced CONCAT function and according to MSDN

> Null values are implicitly converted to an empty string. If all the > arguments are null, an empty string of type varchar(1) is returned.

so it's enough to use CONCAT without IsNull

CONCAT(FirstName, LastName, Email)

SQL Server does not have a CONCAT function.
(Update: Starting from MS SQL Server 2012 it was introduced CONCAT function)

In the default SQL Server behavior, NULLs propagate through an expression.

In SQL Server, one would write:

SELECT FirstName + LastName + Email as Vitals FROM MEMBERS

If you need to handle NULLs:

SELECT ISNULL(FirstName, '') + ISNULL(LastName, '') + ISNULL(Email, '') as Vitals FROM MEMBERS

In the case of MS Access

Option 1) SELECT (FirstName + " " + LastName + " " + Email) as Vitals FROM MEMBERS You will get blank result in the case of any field with null.

Option 2) SELECT (FirstName & " " & LastName & " " & Email) as Vitals FROM MEMBERS You will get Space in place of field with null.

After observing the answers for this question, you may combine all of them into one simple solution

CONCAT_WS(',',
IF(NULLIF(FirstName, '') IS NULL, NULL, FirstName),
IF(NULLIF(LastName, '') IS NULL, NULL, usr_lastname),
IF(NULLIF(Email, '') IS NULL, NULL, Email))

So, in short we use CONCAT_WS to concatenate our fields and separate them with ,; and notice that NULL fields nor EMPTY wont concatenated

NULLIF will check if the field is NULL or EMPTY, a field that contains only spaces or is empty as well, ex: '', ' ') and the output will be either NULL or NOT NULL

IF Will out put the field if it's not NULL or EMPTY