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
The 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
The 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().
Tagged: concat(), Concatenation, Functions, Microsoft SQL Server, New features in SQL Server 2012, Null, SQL, SQL Server 2012
[…] CONCAT() function in SQL Server 2012 (appliedsql.wordpress.com) […]
LikeLike
[…] CONCAT() function in SQL Server 2012 (appliedsql.wordpress.com) […]
LikeLike