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

  • A couple of options come to mind for how to avoid using ISNULL around each column. First of all, only the middle name can be NULL based on the column definitions so an easy solution is to put the ISNULL() or COALESCE() around just the middle name column. That alone would improve performance and readability.

    Ex:
    SELECT emp_name + COALESCE(emp_middlename, ”) + emp_lastname AS Result
    FROM #temp;

    Another technique that I like to use for readability is to use COALESCE() around the entire concatenation.

    Ex:
    SELECT
    COALESCE(emp_name + emp_middlename + emp_lastname ,
    emp_name + emp_lastname) AS Result
    FROM #temp;

    But yet another option that may perform better with an index that includes the middle name would be to do a UNION ALL with and without the middle name depending on if the Middle Name column is NULL.

    Ex:
    SELECT emp_name + emp_middlename + emp_lastname AS Result
    FROM #temp
    WHERE emp_middlename IS NOT NULL
    UNION ALL
    SELECT emp_name + emp_lastname AS Result
    FROM #temp
    WHERE emp_middlename IS NULL

    This avoids the costly function call which may improve performance under certain circumstances.

    -Eric Isaacs

    Reply
    • kiran inaparty
      April 25, 2016 11:15 am

      SELECT emp_name + Case when emp_middlename is null then ” else emp_middlename end + emp_lastname AS Result

      Reply
  • Concat …

    Reply
  • Use concat

    Reply
  • Hello,
    I think CONCAT() will work fine here. But if it is throwing a data type compatibility issue when concatinating Char with Int, we can use data type conversion inside concat itself
    Please correct me if I am wrong here
    Thanks

    Reply
  • Pawan Kumar Khowal
    July 10, 2015 8:47 am

    We can use CONCAT

    SELECT CONCAT(emp_name,emp_middlename,emp_lastname) AS Result

    CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned. The implicit conversion to strings follows the existing rules for data type conversions.

    Pawan Kumar Khowal

    Reply
  • Istekhar Ahmad
    July 10, 2015 9:30 am

    Hi
    We can use concat instead of + because concat handle null values.

    Reply
  • vijendra singh
    July 10, 2015 9:30 am

    We can use a default value while inserting records in table, or we have COALESCE() and ISNULL() functions too to accomplish the task.

    Reply
  • Reply
  • Yogesh Shinde
    July 10, 2015 9:41 am

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

    Reply
  • Yogesh Shinde
    July 10, 2015 9:44 am
    Reply
  • Shrikesh Kale
    July 10, 2015 9:48 am

    use

    select concat(emp_name, emp_middlename, emp_lastname)AS Result FROM #temp;

    Reply
  • Instead of adding ISNULL function and that will increase the processing time.
    According to me, we should alter the column and need to add default constraints to the table column as shown below,

    alter table “Table Name” add constraint “Constraint Name(id)” default for “Column Name”

    Let me know your feedback for the same.

    Reply
  • Udara Madushanka
    July 10, 2015 9:59 am

    USE Concat keyword,
    select concat(emp_name,emp_middlename,emp_lastname) AS Result2
    FROM #temp

    Reply
  • Hi Pinal,

    Another option is by using COALESCE,

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

    Please correct me if I am wrong.

    Reply
  • Hi Pinal dave,
    I think we can use this CONCAT_NULL_YIELDS_NULL setting.
    SET CONCAT_NULL_YIELDS_NULL OFF
    SELECT emp_name + emp_middlename + emp_lastname AS Result
    FROM #temp;
    SET CONCAT_NULL_YIELDS_NULL ON

    Let us know all other possible ways also..

    Reply
  • I have tried with other data types also…
    CREATE TABLE #temp (
    emp_name NVARCHAR(200) NOT NULL,
    emp_middlename VARCHAR(200) NULL,
    emp_lastname int NOT NULL
    );
    INSERT INTO #temp VALUES( ‘SQL’, NULL, 12 );
    INSERT INTO #temp VALUES( ‘SQL’, ‘Server’, 145 );
    SET CONCAT_NULL_YIELDS_NULL OFF
    SELECT emp_name + emp_middlename + CONVERT(VARCHAR(10),emp_lastname) AS Result
    FROM #temp;
    SET CONCAT_NULL_YIELDS_NULL ON
    DROP TABLE #temp;
    GO

    Reply
  • 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;

    /*Findout all NULL allowed columns and update data with below query*/
    Update #temp
    set emp_middlename=ISNULL(emp_middlename,”)

    /*add NOT NULL to those columns*/

    ALTER TABLE #temp
    ALTER COLUMN emp_middlename NVARCHAR(200) NOT NULL

    /*Now find the below query*/
    SELECT emp_name + emp_middlename + emp_lastname AS Result
    FROM #temp;

    /*This is like prevention is better than cure*/

    drop table #temp

    Reply
  • CONCAT
    Null values are implicitly converted to an empty string

    Reply
  • Haseeb Iftikhar
    July 10, 2015 10:41 am

    There will be 2 solutions
    First is to SET CONCAT_NULL_YIELDS_NULL OFF
    Second is to Use COALESCE

    Reply
  • It is a very good puzzle Pinal. You can use CONCAT function, I have shown the example at

    Reply

Leave a Reply

Menu