NULL Concatenation

Have you ever tried to concatenate few fields? in most cases your result was null? It is default behaviour of SQL Server because NULL value cannot be compared with any type of data. Sometimes we need to change this behaviour – how we can do that? I will try to explain that in this article.

SQL Server has built-on property called CONCAT_NULL_YIELDS_NULL. This property controls SQL Server behaviour regarding NULL values for example:

As a result we receive:

concat_nulls

 

 

 

 

 

 

As you can see we can very easily change SQL Server behaviour, nonetheless change this property is not a good thing, similiar result we can receive in more elegant way – using some TSQL function like ISNULL or COALESCE.

 

concat_nulls2

 

 

 

 

 

 

The only difference between these two functions is that ISNULL can replace null with specific value/field and COALESCE is something like nested ISNULL function. I think that if you are familiar with these three keywords give us opportunity to solve most problems associated with concatenating or comparing null values.

Leave a Comment

Your email address will not be published. Required fields are marked *