CONCAT'ing NULL fields

user_2192 0
Viewed 97356

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?

concatenation sql-server sql
11 Answers

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

Stefan_Mai_132570 
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,''))
Hafthor_44890

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

user_1281650

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!

user_1272780

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
Cade_Roux_182550

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.

user_30156850
Related