SQL Server String Concatenation with Null

Question

I am creating a computed column across fields of which some are potentially null   The problem is that if any of those fields is null  the entire computed column will be null  I understand from the Microsoft documentation that this is expected and can be turned off via the setting SET CONCAT NULL YIELDS NULL  However  there I don t want to change this default behavior because I don t know its implications on other parts of SQL Server   Is there a way for me to just check if a column is null and only append its contents within the computed column formula if its not null

User · Answer

Use   SET CONCAT NULL YIELDS NULL  OFF    and concatenation of null values to a string will not result in null   Please note that this is a deprecated option  avoid using  See the documentation for more details

User · Answer

Use COALESCE  Instead of your column use COALESCE your column       This will return the empty string instead of NULL

User · Answer

ISNULL ColumnName

User · Answer

From SQL Server 2012 this is all much easier with the CONCAT function   It treats NULL as empty string  DECLARE  Column1 VARCHAR 50     Foo            Column2 VARCHAR 50    NULL           Column3 VARCHAR 50     Bar     SELECT CONCAT  Column1  Column2  Column3     Returns FooBar

User · Answer

You can use ISNULL        SET  Concatenated   ISNULL  Column1        ISNULL  Column2        If the value of the column expression is indeed NULL  then the second value specified  here  empty string  will be used instead

User · Answer

You can also use CASE - my code below checks for both null values and empty strings  and adds a seperator only if there is a value to follow   SELECT OrganisationName    Address     CASE WHEN Addr1 IS NULL OR Addr1      THEN    ELSE Addr1 END    CASE WHEN Addr2 IS NULL OR Addr2      THEN    ELSE        Addr2 END    CASE WHEN Addr3 IS NULL OR Addr3      THEN    ELSE        Addr3 END    CASE WHEN County IS NULL OR County      THEN    ELSE        County END  FROM Organisations

User · Answer

I just wanted to contribute this should someone be looking for help with adding separators between the strings  depending on whether a field is NULL or not     So in the example of creating a one line address from separate fields     Address1  Address2  Address3  City  PostCode    in my case  I have the following Calculated Column which seems to be working as I want it   case      when  Address1  IS NOT NULL      then               Address1                    isnull       Address2                   isnull       Address3                   isnull       City                       isnull       PostCode        end   Hope that helps someone

User · Answer

In Sql Server   insert into Table Name PersonName PersonEmail  values NULL  xyz xyz com    PersonName is varchar 50   NULL is not a string  because we are not passing with in single codes  so it treat as NULL    Code Behind   string name    txtName Text       NULL        txtName Text       string email   txtEmail Text   insert into Table Name PersonName PersonEmail  values name    email

User · Answer

I had a lot of trouble with this too  Couldn t get it working using the case examples above  but this does the job for me   Replace rtrim ltrim ISNULL Flat no               ISNULL House no               ISNULL Street               ISNULL Town               ISNULL City                    Replace corrects the double spaces caused by concatenating single spaces with nothing between them  r ltrim gets rid of any spaces at the ends

User · Answer

This example will help you to handle various types while creating insert statements  select   insert into doc Id  CDate  Str  Code  Price  Tag        values                  convert nvarchar 50   Id          -- uniqueidentifier              LEFT CONVERT VARCHAR  CDate  120   10          -- date              Str        -- string              convert nvarchar 50   Code           -- int       convert nvarchar 50   Price        -- decimal              ISNULL Tag                        -- nullable string   from doc  where CDate gt   2019-01-01 00 00 00 000

[sql-server] SQL Server String Concatenation with Null

Examples related to sql-server

Examples related to null

Examples related to string-concatenation

Examples related to calculated-columns