CONCAT() function in SQL Server 2012


In this article I will discuss the new function concat() in SQL Server 2012. This function performs a concatenation operation. We need to pass CONCAT() a number of string arguments and it will concatenate, or join them together and return an output string.
The basic syntax is as follows:
CONCAT ( string_value1, string_value2 [, string_valueN ] )

Let us now understand with an example how we can use this function and how it is different from the concatenation operator. Let us create a new table and insert some values in the table and then use a query that will use this function

CREATE TABLE MyConcatTable
(
FirstName varchar(20) NOT NULL,
MiddleName varchar(20) NULL,
LastName varchar(20) NOT NULL
)
INSERT INTO MyConcatTable
VALUES
('Sachin', 'Ramesh', 'Tendulkar'),
('Diego', 'Armando', 'Maradona')
,('Diego', Null, 'Maradona')
SELECT CONCAT(FirstName + ' ', MiddleName + ' ', LastName) AS
CustomerName
FROM MyConcatTable

The output of the above as shown below
concat1The concat function joins the columns and returns a single string. Now the big question is how is it different from the below

SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS CustomerName
FROM MyConcatTable

Let us run the above code and see the outcome
concat2The 3rd value in the table has a null in the middle name column. Normal concatenation would not handle that thus resulting in a null output but the concat() would automatically remove the null and join the next corresponding string value.

I hope this short article was helpful in understanding the new function in SQL Server 2012 called concat().

Advertisements

Tagged: , , , , , , ,

2 thoughts on “CONCAT() function in SQL Server 2012

  1. […] CONCAT() function in SQL Server 2012 (appliedsql.wordpress.com) […]

    Like

  2. […] CONCAT() function in SQL Server 2012 (appliedsql.wordpress.com) […]

    Like

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

%d bloggers like this: