In SQL Server 2012, there are two new string functions being introduced, namely: CONCAT(), FORMAT(). In this blog post we are going to learn about String Function CONCAT(). CONCAT takes a minimum of two arguments to concatenate them, resulting to a single string.
Now let us look at these examples showing how CONCAT() works:
Example 1: CONCAT Function Usage
SELECT CONCAT(1, 2, 3, 4) AS SingleString
SELECT CONCAT('One',1, 1.1, GETDATE()) AS SingleString
SELECT CONCAT('One',2,NULL) AS SingleString
SELECT CONCAT('','','','') AS SingleString
SELECT CONCAT(NULL, NULL) AS SingleString
Now let us observe a few things based on the result above. Earlier, when we had to concat strings, we used ‘+’ sign and always CAST/CONVERT any variable to a string. It used to give us an error. However, when you look at this new one function, it automatically and implicitly CAST/CONVERT any datatype to integer and then CONCATs them together in a single string. NULL values are automatically converted to empty strings. If you notice that even the datetime fields are automatically converted to the string without any extra operations. Additionally, the return value from the CONCAT string could be of datatype VARCHAR(MAX).
Example 2: Usage of CONCAT with Table
USE [AdventureWorks2008R2] GO SELECT CONCAT([AddressID],' ',[AddressLine1],' ', [AddressLine2],' ',[City],' ', [StateProvinceID],' ',[PostalCode]) AS Address FROM [Person].[Address] GO
In the following example, we see the result of the query listed above:
You can see how neatly and easily the strings are concatenated using this new function. It takes out lots of unnecessary code and makes it much simpler to execute.
Now let us look under the hood in the execution plan. In the execution plan, we can see that the CONCAT function is a scalar operation. When we look at the scalar operation using Properties, it shows that the CONVERT_IMPLICIT function is automatically called to convert non-nvarchar data types columns to NVARCHAR.
Overall, when I have to concat multiple values and data in the future, I am going to use the CONCATE() function.
Watch a quick video relevent to this subject:
Reference: Pinal Dave (https://blog.sqlauthority.com)
17 Comments. Leave new
Nice article sir. It was mentioned as CONCATE() in the bottom line. Plz check it.
Really nice one. Thanks. Cool thing is – we can use as concatenate between 2 and 254 values in a single go.
Yeah, nice one. I would recommend programmers to spend some time on this and start using CONCAT() more ;>) and I too would like to keep this in mind for usage…..
Nice article . Thanks .
Example 2 contains description for choose function instead of CONCAT description
I like this alot. Excellent post and just the sort of thing I need to know about Denali.
One question though please…
Is there a maximum size to the output – I assume if the string size goes high enough it just returns a nvarchar(max) – Is this correct?
Dave
Hi Pinal, I am looking for some good book / site for designing SSIS. I have fair understanding of SSIS. Something that can go discuss in detail the pros and cons of embedding SQL in SSIs vs SSIS call stored proc and some parallalism on SQL Server / SSIS. Recently we found that if Biztalk is installed in the same instance of ur application database , it will change the maxtop to 1 and have significant impact on application performance. Shed some light on this topic guru.
And above all, have a nice weekend :)
Nice article. I did notice that in the Example 2 section you have accidentally mentioned the CHOOSE() function and talk about how you can use that to find if a day is weekday or weekend…. followed by the example of CONCAT().
You may want to erase the CHOOSE() part…
Thanks,
Aashish
Hi Pinal, Please can we have a post that lists all the new features in Denali… what we have here is fabulous, but its very difficult to remember. Some cheat sheet / quick glance would help .
Hope i am not asking for much.
hi pinal,
it’s very nice article but i want to ask you one thing that after concatenation of date & varchar field or any different data type fields, what’s the datatype of new cancatenated field?
It will be datetime datatype if the concatenated string is a valid date
hi I am trying to use concat function in SQL server 2008 using mgmt studio bit is gives me error saying Concat function doesn’t exist
Select concat([DiagramInfoID],”,[Date_Review_Complete]) as A from Bcontrol_ReviewImport
sonu- this syntax will be worked in sql server 2012.
Great, thank you pinal
Great help this will be. I have to try this out. Just a question though. Might not be a very intelligent one.
What if it is
select Concat(LastName , ‘ ‘, Firstname) as Name from Sometable
Where LAstName is null and Firstname is Romi
Will it return Romi with a leading space as Name?
Good news to see concat function in 2012.
InMY SQL it. Is der for long years back.
Any way its more helpful to programmers