concatenate two database columns into one resultset column

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?

The SQL standard way of doing this would be:

SELECT COALESCE(field1, '') || COALESCE(field2, '') || COALESCE(field3, '') FROM table1

Example:

INSERT INTO table1 VALUES ('hello', null, 'world');
SELECT COALESCE(field1, '') || COALESCE(field2, '') || COALESCE(field3, '') FROM table1;

helloworld

If you were using SQL 2012 or above you could use the CONCAT function:

SELECT CONCAT(field1, field2, field3) FROM table1

NULL fields won't break your concatenation.

@bummi - Thanks for the comment - edited my answer to correspond to it.

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