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
How about using SET CONCAT_NULL_YIELDS_NULL OFF? Not tested but will work by specifying the above in specific context
I will use CONCAT function.
Use COALESCE
SELECT COALESCE(emp_name, emp_middlename, emp_lastname)
This will not concatenate all the columns. It will just pick up the first non null value
SELECT COALESCE(emp_name,”)+COALESCE(emp_middlename,”)+COALESCE(emp_lastname,”)
FROM #temp;
SELECT CONCAT(emp_name,emp_middlename,emp_lastname)
FROM #temp;
— 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.
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
SELECT emp_name + emp_middlename + emp_lastname AS Result , CONCAT(emp_name ,emp_middlename ,emp_lastname)
FROM #temp;
Use concat .
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
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
we can use CONCAT() function if we are using the SQL server 2012… CONCAT function is not available till sql 2008 R2…
SET CONCAT_NULL_YIELDS_NULL OFF; this will off the setting for concluding NULL behaviour.
SET CONCAT_NULL_YIELDS_NULL OFF; this will OFF the setting for concluding NULL behaviour of Sql server.
For all the columns from which we are pulling the data we need to add the constraint “NOT NULL”
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
);
Use ISNULL()
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.
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….
You are correct. This can be done in sql only if you want to export concatenated values into a csv or text file
For the SQL Server Versions before 2012 use SET CONCAT_NULL_YIELDS_NULL OFF
For later versions you can use CONCAT() function
You can use ” COALESCE ” function – that replaces null value into an empty string.
SELECT emp_name + COALESCE(emp_middlename, ”) + emp_lastname AS Result
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
Hi Pinal,
Please let us know the correct answer.
SELECT concat(lastname + ‘, ‘, firstname, ‘ ‘ + middlename) AS Result
FROM #temp