CONCAT'ing NULL fields

Question

I have a table with three fields, FirstName, LastName and Email.

Here's some dummy data:

FirstName | LastName | Email
Adam        West       [email protected]
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 1

Try

ISNULL(FirstName, '<BlankValue>')

So,

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

would return the same thing without the null issue (and a blank string where nulls should be).

Answer 2

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!

Answer 3

SQL Server does not have a 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

Answer 4

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

Answer 5

You can always use the CONCAT_NULL_YIELDS_NULL setting..

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