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:

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)

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

  • Select CONCAT(emp_name,emp_middlename,emp_lastname) AS result FROM #temp

    Reply
  • How about using SET CONCAT_NULL_YIELDS_NULL OFF? Not tested but will work by specifying the above in specific context

    Reply
  • I will use CONCAT function.

    Reply
  • Use COALESCE
    SELECT COALESCE(emp_name, emp_middlename, emp_lastname)

    Reply
  • SELECT COALESCE(emp_name,”)+COALESCE(emp_middlename,”)+COALESCE(emp_lastname,”)
    FROM #temp;
    SELECT CONCAT(emp_name,emp_middlename,emp_lastname)
    FROM #temp;

    Reply
  • — Solution #1
    select concat(emp_name,emp_middlename,emp_lastname) from #temp;

    — Solution #2
    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
    ) AS Result
    FROM #temp;

    I would suggest the first one as it looks tidy and clean.
    However, if you notice the execution plan for both takes equal amount of resources. So both are equally efficient – I guess :)

    Folks, please share your view on it.

    Reply
  • Alahari Jayendra Prasad
    July 10, 2015 11:30 am

    Hi Pinal,

    By setting CONCAT_NULL_YIELDS_NULL off, concatenation of null values to a string will not result in null.

    Below is the script:
    SET CONCAT_NULL_YIELDS_NULL OFF

    SELECT
    emp_name + emp_middlename + emp_lastname AS Result
    FROM NullHandling

    Thanks
    Alahari Jayendra Prasad

    Reply
  • Nihar Kulkarni
    July 10, 2015 12:22 pm

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

    Use concat .

    Reply
  • As CONCAT is available in SQL Server2012, to work with previous versions of MSSQL 2012 we can make use of CONCAT_NULL_YIELDS_NULL OFF option…

    SET CONCAT_NULL_YIELDS_NULL OFF
    SELECT emp_name + emp_middlename + emp_lastname AS Result
    FROM #temp;
    SET CONCAT_NULL_YIELDS_NULL ON

    Reply
  • Hi Pinal,

    For versions below SQL Server 2012 you can use “SET CONCAT_NULL_YIELDS_NULL OFF”
    And for SQL 2012 we can use concate() function

    Regards
    Bhanu kandregula

    Reply
  • we can use CONCAT() function if we are using the SQL server 2012… CONCAT function is not available till sql 2008 R2…

    Reply
  • SET CONCAT_NULL_YIELDS_NULL OFF; this will off the setting for concluding NULL behaviour.

    Reply
  • SET CONCAT_NULL_YIELDS_NULL OFF; this will OFF the setting for concluding NULL behaviour of Sql server.

    Reply
  • For all the columns from which we are pulling the data we need to add the constraint “NOT NULL”

    Reply
    • I mean the second column should also have the NOT NULL constraint as mentioned below ..

      CREATE TABLE #temp (
      emp_name NVARCHAR(200) NOT NULL,
      emp_middlename NVARCHAR(200) NOT NULL,
      emp_lastname NVARCHAR(200) NOT NULL
      );

      Reply
  • Carlos A Sanchez (@iscenigmax)
    July 10, 2015 8:45 pm

    Use ISNULL()

    Reply
  • CONCAT() is what I would use.

    Another trick I use for queries similar to this is when I want to display person names in ‘LastName, FirstName MiddleName’ format. I actually combine CONCAT() and the + operator as such:

    CONCAT(LastName + ‘, ‘,FirstName,’ ‘ + MiddleName) AS PersonName

    This way, if the person only has a first name or doesn’t have a middle name, the results would be ‘FirstName’ or ‘LastName, FirstName’. The extra commas and spaces are omitted because the + operator will return a NULL if the names don’t exist and the CONCAT() will convert it to an empty string.

    Reply
  • This is remarkably similar to a problem we had at work a couple days ago, where a SELECT clause concatenated together 17 columns (and a lot of text) along with numerous (but not enough) isnull()s, plus a bunch of case/whens all to build a single, very long string which looked like XML.

    So, the answer for my problem (and by extension, the puzzle here, since I assume it was just a stand-in for a long concatenation) is : Let the data server do what it was designed to do — return a block of data. All string formatting should be done in the host language….

    Reply
    • You are correct. This can be done in sql only if you want to export concatenated values into a csv or text file

      Reply
  • For the SQL Server Versions before 2012 use SET CONCAT_NULL_YIELDS_NULL OFF

    For later versions you can use CONCAT() function

    Reply
  • Puneet Chawla
    July 11, 2015 5:25 pm

    You can use ” COALESCE ” function – that replaces null value into an empty string.

    SELECT emp_name + COALESCE(emp_middlename, ”) + emp_lastname AS Result

    Reply
    • The problem is you do not know which column has null and you need to apply COALESCE on all columns. The simple method is to use CONCAT function as shown at

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

    Reply

Leave a Reply

Menu