CONCAT'ing NULL fields

sql sql-server concatenation
Thomas R picture Thomas R · Jun 24, 2009 · Viewed 91.5k times · Source

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

Stefan Mai picture Stefan Mai · Jun 24, 2009

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

Related questions

SQL, How to Concatenate results?

I currently have a SQL query that returns a number of fields. I need one f the fields to be effectively a sub query sub that. The Problem in detail: If I have a table X with two columns, ModuleID …

Read More
How to concatenate strings and commas in SQL Server?

I'm relatively new to MSSQL, so sorry if the question might sounds trivial. I want to concatenate multiple fields with a delimiter ,. However, when the field is empty, the extra , will be included in the result string as well. So …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More