concatenate two database columns into one resultse

问题:

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?

回答1:

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

回答2:

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.

回答3:

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

回答4:

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

回答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 preserve the concatentation operation from evaluating as an overall NULL result.

回答6:

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

回答7:

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