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

Solarwinds

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

SQL SERVER - Puzzle - Working with functions to Concatenate columns concatenate-quiz-01

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)

Solarwinds
Previous Post
SQL SERVER – Using Project Connections in SSIS – Notes from the Field #088
Next Post
POSTGRESQL – How to Create Function? – How to Declare Local Variable

Related Posts

No results found

52 Comments. Leave new

  • David W. Madden
    July 14, 2015 6:23 pm

    SELECT concat(lastname + ‘, ‘, firstname, ‘ ‘ + middlename) AS Result
    FROM #temp

    Reply
  • Hi Pinal,
    Please let us know the correct answer.

    Reply
    • You know the reply :) … It is using CONCAT function. I think most of you got it correct :) …

      Reply
  • Satish Vellanki
    June 24, 2016 5:17 pm

    here the order will changing please check @Eric Isaacs

    Reply
  • Abhinav Pandey
    August 17, 2016 3:19 pm

    SELECT ISNULL(emp_name,”) + ISNULL(emp_middlename,”) + ISNULL(emp_lastname,”) AS Result
    FROM #temp;

    Reply
  • SELECT isnull(emp_name,”) + isnull(emp_middlename,”) + isnull(emp_lastname,”) AS Result FROM #temp;

    Reply
  • jayakumarjkumar
    August 23, 2016 4:54 pm

    SELECT case when emp_name IS NULL THEN ” ELSE emp_name END +
    CASE WHEN emp_middlename IS NULL THEN ” ELSE emp_middlename END +
    CASE WHEN emp_lastname IS NULL THEN ” ELSE emp_lastname END SQLNAME
    FROM #temp

    Reply

Leave a Reply

Menu