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

Concatenating  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
SELECT CAST(1 AS VARCHAR(10)) + ' ' + CAST(2 AS VARCHAR(10))
-- 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?

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

Database, SQL in Sixty Seconds, SQL Scripts, SQL Server Management Studio
Previous Post
SQL SERVER – Fix: Error : 402 The data types ntext and varchar are incompatible in the equal to operator
Next Post
SQL SERVER – Caption the Cartoon Contest – Last 2 Days

Related Posts

5 Comments. Leave new

  • Cool !

    Reply
  • Thanks Pinal for this info!

    Reply
  • Andre Ranieri
    December 6, 2012 6:07 am

    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?

    Thanks,

    Andre Ranieri

    Reply
  • This helps alot. Thanks alot.

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

    Reply

Leave a Reply

Menu
Exit mobile version