concatenate two database columns into one resultset column

Asked 2011-06-21T15:21:14.857

I use the following SQL to concatenate several database columns from one table into one column in the result set:

SELECT (field1 + '' + field2 + '' + field3) FROM table1

When one of the fields is null I got null result for the whole concatenation expression. How can I overcome this?

The database is MS SQL Server 2008. By the way, is this the best way to concatenate database columns? Is there any standard SQL doing this?

sql sql-server-2008 concatenation

Edit Report

1 Answer

Normal behaviour with NULL is that any operation including a NULL yields a NULL...

- 9 * NULL  = NULL  
- NULL + '' = NULL  
- etc

To overcome this use ISNULL or COALESCE to replace any instances of NULL with something else..

SELECT (ISNULL(field1,'') + '' + ISNULL(field2,'') + '' + ISNULL(field3,'')) FROM table1

answered 2011-06-21T15:23:27.290