SQL SERVER – Denali – String Function – CONCAT() – A Quick Introduction

In SQL Server Denali, there are two new string functions being introduced, namely:

CONCAT()
FORMAT()

Today we will quickly take a look at the CONCAT() function. 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 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 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 datatypes 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 (http://blog.SQLAuthority.com)

About these ads

27 thoughts on “SQL SERVER – Denali – String Function – CONCAT() – A Quick Introduction

  1. 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…..

  2. 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

  3. 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 :)

  4. 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

  5. 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.

  6. Pingback: SQL SERVER – Denali – 14 New Functions – A Quick Guide « Journey to SQLAuthority

  7. 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?

  8. Pingback: SQL SERVER – A Quick Trick about SQL Server 2012 CONCAT function « SQL Server Journey with SQLAuthority

  9. Pingback: SQL SERVER – A Quick Trick about SQL Server 2012 CONCAT Function – PRINT « SQL Server Journey with SQLAuthority

  10. Pingback: SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function « SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Function: Is Function – SQL in Sixty Seconds #004 – Video « SQL Server Journey with SQL Authority

  12. 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

  13. Pingback: SQL SERVER – 2012 Functions – FORMAT() and CONCAT() – An Interesting Usage « SQL Server Journey with SQL Authority

  14. Pingback: SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video « SQL Server Journey with SQL Authority

  15. Pingback: SQL SERVER – Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT in SQL Server 2012 onwards | SQL Server Journey with SQL Authority

  16. 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?

  17. Good news to see concat function in 2012.
    InMY SQL it. Is der for long years back.
    Any way its more helpful to programmers

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  19. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s