Merge two database columns into one result column

Question

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

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

When one of the fields is null, I got a 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?

Answer 1

The standard SQL way to do this:

 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 

Answer 2

If you used SQL 2012 or higher, you can use the CONCAT function:

 SELECT CONCAT(field1, field2, field3) FROM table1 

NULL fields will not violate your concatenation.

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

Answer 3

The normal behavior with NULL is that any operation, including NULL, yields NULL ...

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

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

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

Answer 4

If you have a problem with NULL values, use the COALESCE function to replace NULL with the value of your choice. Then your request will look like this:

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

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

Answer 5

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 keep the concatenation operation from evaluating as a common NULL result.

Answer 6

If both columns 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 7

Just Cast Column As Varchar (Size)

If both columns are numeric, use the 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