SOLVE NULL + Text = NULL OR NULL + Int = NULL

Scenario: 

I have two columns in my table which are of type varchar, Now when I try to concatenate these two columns where one of columns have NULL it will lead to NULL which is wrong. Same thing with columns of type Int, if I try adding NULL int with non NULL int it will lead to NULL which is wrong.

From above data if I concatenate Text1 with Text2 it will result to NULL. Query for concatenating and its result is as follows:

Query:

Select Text1+Text2 As [Text1+Text2] from [dbo].[Null_Test]

Result: 

Solution: 

Replace NULL with empty string '' which will solve above issue. To achieve this you can use ISNULL or Coalesce. Below are two queries with ISNULL and Coalesce.

ISNULL Query:

Select Text1+ISNULL(Text2,'') As [Text1+Text2] from [dbo].[Null_Test]

Result: 

Coalesce Query:

Select Text1+Coalesce(Text2,'') As [Text1+Text2] from [dbo].[Null_Test]

Result: 

From above data if I concatenate Num1 with Num2 it will result to NULL. Query for adding and its result is as follows:

Query:

Select Num1+Num2 As [Num1+Num2] from [dbo].[Null_Test]

Result: 

Solution: 

Replace NULL with 0 which will solve above issue. To achieve this you can use ISNULL or Coalesce. Below are two queries with ISNULL and Coalesce.

ISNULL Query:

Select Num1+ISNULL(Num2,0) As [Num1+Num2] from [dbo].[Null_Test]

Result: 

Coalesce Query:

Select Num1+Coalesce(Num2,0) As [Num1+Num2] from [dbo].[Null_Test]

Result:

Conclusion: 

Null + anything is NULL in SQL Server. 

Split Comma Seperated Values In a Single Cell - SQL Server

Scenario: 

Let us say that I have Medical Product which is manufactured from one or more molecules and data is given by customer in below format:

Product Molecule Comma separated data

Now I want to store data in below format

Comma Separated Values Split Into Rows 

Below is the query which can be used to achieve this:  

WITH Cte AS (

    SELECT Product,

        CAST('<M>' + REPLACE( Molecules,  ',' , '</M><M>') + '</M>' AS XML) AS Molecules

    FROM dbo.ProductMolecules

 )

SELECT

    Product,

    Split.a.value('.', 'VARCHAR(100)') AS Molecules

FROM Cte

CROSS APPLY Molecules.nodes('/M') Split(a);

1) CTE is used to convert comma seperated values into XML format like "<M>X</M><M>Y</M><M>Z</M>"
2) XML holds data in nodes.
3) Cross apply needs Left and Right objects.
4) Your left object is CTE which is holding its data, right object is Molecules column casted to XML data type which you are finding nodes.
5) The data identified in each node will be stored in alias Split(a); Split(a) can be anything like XYZ(AB).
5) Now since you have data splitted internally by XML you are just calling Split.a.Value by again converting nodes data to varchar.6) This takes care of splitting data and hence final result is achieved.