concatenate two database columns into one resultset column

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] concatenate two database columns into one resultset column

Examples related to sql

Examples related to sql-server-2008

Examples related to concatenation