SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

http://www.pinaldave.com/bimg/sixty00.jpgConcatenating  string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL Server 2012 has introduced new function CONCAT which concatenates the strings much efficiently.

When we concat values with ‘+’ in SQL Server we have to make sure that values are in string format. However, when we attempt to concat integer we have to convert the integers to a string or else it will throw an error. However, with the newly introduce the function of CONCAT in SQL Server 2012 we do not have to worry about this kind of issue. It concatenates strings and integers without casting or converting them. You can specify various values as a parameter to CONCAT functions and it concatenates them together.

Let us see how to concat the values in Sixty Seconds:

Here is the script which is used in the video.

-- Method 1: Concatenating two strings
SELECT 'FirstName' + ' ' + 'LastName' AS FullName
-- Method 2: Concatenating two Numbers
-- Method 3: Concatenating values of table columns
SELECT FirstName + ' ' + LastName AS FullName
FROM AdventureWorks2012.Person.Person
-- Method 4: SQL Server 2012 CONCAT function
SELECT CONCAT('FirstName' , ' ' , 'LastName') AS FullName
-- Method 5: SQL Server 2012 CONCAT function
SELECT CONCAT('FirstName' , ' ' , 1) AS FullName

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

5 thoughts on “SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

  1. It’s my understanding that the SQL 2012 CONCAT() function eliminates the need to set CONCAT_NULL_YIELDS_NULL off. Can you confirm if this is correct?

    In your method #5, is there any overhead in the execution plan from an implicit datatype conversion?


    Andre Ranieri


  2. Just a note that if you are doing a FIELD1+FIELD2 as FIELD3 when either one of the fields are NULL then it will not work probably. You will need to perform a isnull(FIELD1,”)+isnull(FIELD2,”) as FIELD3


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