CODEWITHSUNDEEP
sqlsql-serverconcatenation
Thomas R
Thomas R

Reputation: 3036

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       [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?

Upvotes: 61

Views: 102786

Answers (11)

Cade Roux
Cade Roux

Reputation: 89661

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

Upvotes: 2

Michael Freidgeim
Michael Freidgeim

Reputation: 28435

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)

Upvotes: 3

Eiad Samman
Eiad Samman

Reputation: 407

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

Upvotes: 0

BILBO
BILBO

Reputation: 729

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.

Upvotes: 72

vaibhav sarode
vaibhav sarode

Reputation: 11

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.

Upvotes: 1

Stefan Mai
Stefan Mai

Reputation: 23939

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

Upvotes: 126

jmarceli
jmarceli

Reputation: 20162

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

Upvotes: 4

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195982

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

Upvotes: 5

chaladi
chaladi

Reputation: 365

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

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

Upvotes: 12

Hafthor
Hafthor

Reputation: 16896

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

Upvotes: 6

Brian
Brian

Reputation: 2261

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!

Upvotes: 3

Related Questions