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