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?
This question is related to
sql
sql-server-2008
concatenation
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
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.
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.
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
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
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
Source: Stackoverflow.com