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

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

SQL SERVER 2012 - String Function CONCAT() - A Quick Introduction concat1

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.
SQL SERVER 2012 - String Function CONCAT() - A Quick Introduction concat4

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.

SQL SERVER 2012 - String Function CONCAT() - A Quick Introduction concat3

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)

, , ,
Previous Post
SQL SERVER – Introduction to expressor 3.4 Lookup Tables
Next Post
SQL SERVER – Denali – String Function – FORMAT() – A Quick Introduction

Related Posts

17 Comments. Leave new

  • Nice article sir. It was mentioned as CONCATE() in the bottom line. Plz check it.

    Reply
  • Really nice one. Thanks. Cool thing is – we can use as concatenate between 2 and 254 values in a single go.

    Reply
  • 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…..

    Reply
  • Partha Pratim Dinda
    September 16, 2011 2:33 pm

    Nice article . Thanks .

    Reply
  • Example 2 contains description for choose function instead of CONCAT description

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

    Reply
  • 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 :)

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

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

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

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

    Reply
  • Great, thank you pinal

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

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

    Reply

Leave a Reply

Menu