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)
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
SELECT emp_name + Case when emp_middlename is null then ” else emp_middlename end + emp_lastname AS Result
Concat …
Use concat
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
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
Hi
We can use concat instead of + because concat handle null values.
We can use a default value while inserting records in table, or we have COALESCE() and ISNULL() functions too to accomplish the task.
https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-2017
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT emp_name + emp_middlename + emp_lastname AS Result
FROM #temp;
use
select concat(emp_name, emp_middlename, emp_lastname)AS Result FROM #temp;
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.
USE Concat keyword,
select concat(emp_name,emp_middlename,emp_lastname) AS Result2
FROM #temp
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.
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..
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
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
CONCAT
Null values are implicitly converted to an empty string
There will be 2 solutions
First is to SET CONCAT_NULL_YIELDS_NULL OFF
Second is to Use COALESCE
It is a very good puzzle Pinal. You can use CONCAT function, I have shown the example at
Select CONCAT(emp_name,emp_middlename,emp_lastname) AS result FROM #temp