CONCAT is a new String function introduced in SQL Server 2012. This function returns an output which is a concatenated string value of the argument values passed in the function. The function would need a minimum of 2 values to be passed.
Let’s take a look at the function. In order to use this function, we shall first create a table and insert few records. The following script is used to the task of creating a table and inserting the data into that table.
CREATE TABLE [dbo].[Customer_Address]( [CustomerID] [bigint] NULL, [Address1] [varchar](50) NULL, [Address2] [varchar](50) NULL, [ZipCode1] [char](5) NULL, [ZipCode2] [char](4) NULL ) INSERT INTO Customer_Address VALUES (1,'#1','Moon Walk Drive', 24578,2881 ), (2,'#2','Roof Top Lane', 54856,5421 ), (3,'#3','Full Thottle Blvd', 90425,5782 ), (4,'#4','Drive Slow Road', 18854,6502 )
First let us look at the data in the table by doing a Select * on the table. The pic below displays the result set.
Now let us run the following query which uses the CONCAT function to concatenate the Address1 ad Address2 columns as a single column and also concatenate the 5 character length ZipCode1 and 4 character length Zipcode2 and display a single column output. Note that I am going to use a space ‘ ‘, and a hyphen ‘-‘ in order to display the concatenated column in a meaningful way.
SELECT CustomerID, CONCAT(Address1,' ',Address2) AS Address, CONCAT(ZipCode1,'-',ZipCode2) AS ZIP FROM Customer_Address
The below picture shows the result of the query we have just executed.
In previous Versions of SQL Server, you could concatenate string values using a +. If order to achieve the same result you can write the following code.
SELECT CustomerID, Address1 + ' ' + Address2 AS Address, ZipCode1 + '-' + ZipCode2 AS ZIP FROM Customer_Address
Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.
— Bru Medishetty
