String concatenation issue related to NULL values and solution for it

I am writing this blog to avoid issues related to string concatenation.

“+” is used to concatenate strings, prior to 2012. As you can see in the query below, two strings are being concatenated. Unfortunately this query doesn’t return expected results and it returns NULL.

To fix that, select query should be rewritten to replace null.

In Sql Sever 2012, Concat() is clearly a simpler way to concatenate strings. It improves code readability and returns the expected results since it automatically (implicitly) converts null to blank space.

CREATE TABLE #temp (

emp_name nvarchar(200) NOT NULL,

emp_middlename nvarchar(200) NULL,

emp_lastname nvarchar(200) NOT NULL

);

INSERT INTO #temp VALUES( 'Something', NULL, ' US' );

INSERT INTO #temp VALUES( 'Something', NULL, ' India' );

SELECT CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result FROM #temp;

DROP TABLE #temp

You can use concat function instead of "+" operator.

Happy Coding!!