concatenate two database columns into one resultse

Question

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?

Answer 1

Just Cast Column As Varchar(Size)

If both Column are numeric then use code below.

Example:

Select (Cast(Col1 as Varchar(20)) + '-' + Cast(Col2 as Varchar(20))) As Col3 from Table

What will be the size of col3 it will be 40 or something else

Answer 2

If you are having a problem with NULL values, use the COALESCE function to replace the NULL with the value of your choice. Your query would then look like this:

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

http://www.codeproject.com/KB/database/DataCrunching.aspx

Answer 3

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.

Answer 4

Use ISNULL to overcome it.

Example:

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

This will then replace your NULL content with an empty string which will preserve the concatentation operation from evaluating as an overall NULL result.

Answer 5

If both Column are numeric Then Use This code

Just Cast Column As Varchar(Size)

Example:

Select (Cast(Col1 as Varchar(20)) + '-' + Cast(Col2 as Varchar(20))) As Col3 from Table

Answer 6

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