SQL SERVER – Puzzle – Working with functions to Concatenate columns

Every now and then I get pulled into coding review sessions and those are some of the fun times I every have working with T-SQL of any sorts. This blog is inspired by one such time where I saw at a customer location a code that was lengthy and in one location I was seeing close to 15 fields which were concatenated to form a single column as part of output.

I generally don’t get into the reasoning of it, but what was interesting is they were using ISNULL in front of every column because they were not sure how whole column values to be returned as NULL. To give you an idea, here is a mini-repro to the same problem.

CREATE TABLE #temp (
emp_name NVARCHAR(200) NOT NULL,
emp_middlename NVARCHAR(200) NULL,
emp_lastname NVARCHAR(200) NOT NULL
);
INSERT INTO #temp VALUES( 'SQL', NULL, 'Authority' );
INSERT INTO #temp VALUES( 'SQL', 'Server', 'Authority' );
SELECT emp_name + emp_middlename + emp_lastname AS Result
FROM #temp;
DROP TABLE #temp;
GO


Now, you can see the output by default will look like:

As you can see, the problem is getting the first value as NULL. Customers sometimes don’t want this behavior.

Question for the Day

What would you do in this situation? Would you add an ISNULL in front of every column because you are not sure which of these might be NULL? Can you think of a solution without using the ISNULL function?

Give me your solution over comments. It is very easy – trust me. In my example the customer was also trying to use various Datatypes concatenating into a single column.

Reference: Pinal Dave (https://blog.sqlauthority.com)