Concatenating SQL Server strings using Null

Question

I am creating a calculated column in fields, some of which are potentially null.

The problem is that if any of these fields is null, the entire calculated column will be zero. I understand from the Microsoft documentation that this is expected and can be disabled using the SET CONCAT_NULL_YIELDS_NULL parameter. However, I do not want to change this default behavior because I do not know its implications for other parts of SQL Server.

Is there a way to check if a column is null and only add its contents to a formula with a computed column if its value is not null?

Answer 1

You can use ISNULL(....)

 SET @Concatenated = ISNULL(@Column1, '') + ISNULL(@Column2, '') 

If the value of the column / expression is really NULL, then the second value will be used instead (here: empty string).

Answer 2

From SQL Server 2012, this is much easier with the CONCAT function.

It treats NULL as an empty string

 DECLARE @Column1 VARCHAR(50) = 'Foo', @Column2 VARCHAR(50) = NULL, @Column3 VARCHAR(50) = 'Bar'; SELECT CONCAT(@Column1,@Column2,@Column3); /*Returns FooBar*/ 

Answer 3

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

Answer 4

Use

 SET CONCAT_NULL_YIELDS_NULL OFF 

and concatenating null values ​​to a string will not result in null.

Please note that this is an obsolete option, do not use. See the documentation for more information.

Answer 5

You can also use CASE - my code below will check for both null values ​​and empty lines and add a separator only if there is the following value:

 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 

Answer 6

ISNULL(ColumnName, '')

Answer 7

I just wanted to do my part if someone would seek help with adding separators between the lines, depending on whether the field is NULL or not.

So, in the example of creating the address of one line from separate fields

Address1 , Address2 , Address3 , City , Zip / Postal Code

in my case, I have the following calculated column, which seems to work the way I want it:

 case when [Address1] IS NOT NULL then ((( [Address1] + isnull(', '+[Address2],'')) + isnull(', '+[Address3],'')) + isnull(', '+[City] ,'')) + isnull(', '+[PostCode],'') end 

Hope this helps someone!

Answer 8

On the Sql server:

 insert into Table_Name(PersonName,PersonEmail) values(NULL,'[e-mail]') PersonName is varchar(50), NULL is not a string, because we are not passing with in single codes, so it treat as NULL. 

Code for:

 string name = (txtName.Text=="")? NULL : "'"+ txtName.Text +"'"; string email = txtEmail.Text; insert into Table_Name(PersonName,PersonEmail) values(name,'"+email+"') 

Answer 9

I also had problems with this. Failed to get it to work with the 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 fixes double spaces caused by combining single spaces between them. r / ltrim gets rid of any spaces at the ends.