[sql-server] SQL Server String Concatenation with Null

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?

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.

In Sql Server:

insert into Table_Name(PersonName,PersonEmail) values(NULL,'[email protected]')

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+"')

Use COALESCE. Instead of your_column use COALESCE(your_column, ''). This will return the empty string instead of NULL.

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*/

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 

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!

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.

ISNULL(ColumnName, '')

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> '2019-01-01 00:00:00.000'