SQL Concat String Function in Transact-SQL with SQL Server 2012

SQL CONCAT() string function in SQL Server enables developers return concatenated values of two or more string values. The new SQL string function CONCAT takes N number of input string variables (or at least can be converted to string) and returns a string value which is the concatenation of all input string values. The maximum number of input arguments can be 254.

SQL Concat function can be also implemented using "+" sign. But as a t-sql developer you should take NULL string values while using "+" operator for string concatenation. But SQL Server Concat function provides a safe string concatenation method for developers.

An other benefit for SQL programmers in using SQL Server CONCAT() concatenate string function is the ability of automatic conversion of non-string values into character variables or automatic casting of numeric values into string.

SQL CONCAT() String Concatenation Function with Numeric Values

SQL developers will notice that input argument 2012 is a numeric integer value in the following T-SQL SELECT statements. But the sql engine is successfull in string concatenation process using T-SQL CONCAT() function and it converts numeric value 2012 automatically into a character value.


SELECT CONCAT('SQL Server ', 2012)

Code

SQL Server Concat() string concatenation function example

On the other hand, there is a failure in classic string building using "+" sign.


SELECT 'SQL Server ' + 2012

Code

conversion failed when converting the character value to data type int

In previous versions of SQL Server before SQL Server 2012 Denali CTP3, T-SQL developers should convert the numeric value into character value then use the converted arguments in "+" method


SELECT 'SQL Server ' + CAST(2012 as varchar(10))

Code

SQL string concatenation with numbers using CAST function

T-SQL String Concatenation Function CONCAT() with NULL Values

Here is a simple SQL string concatenation using T-SQL Concat function. You can see that the NULL value is converted into empty string and used in concatenate function. Dealing with NULL input parameters without causing an error or missing of not null string arguments is great for sql developers.


SELECT CONCAT('SQL', 'Server', 2012, NULL, 'Denali')

Code

Even all input string variables are NULL, the output of the SQL Concat function returns empty string.


SELECT CONCAT(NULL,NULL)

Code

SQL Server CONCAT() Function Input Arguments Limitation

As I noted the SQL Server Concat() function takes more than one input parameter. If the string concatenation function takes single parameter, the error message "The concat function requires 2 to 254 arguments" is thrown by SQL Engine.


SELECT CONCAT('CONCAT')

Code

Msg 189, Level 15, State 1, Line 1 The concat function requires 2 to 254 arguments.

What to Expect from SQL CONCAT() String Concatenation Function in Future

One of the most common use of SQL string concatenation is forming the fullname of a person.


SELECT

 CONCAT(FirstName, MiddleName, LastName) FullName,

 FirstName, MiddleName, LastName

FROM Person.Person

Code

SQL string concatenation function CONCAT()

As a T-SQL programmer, what I expect from Microsoft SQL Engine teams is to extend the CONCAT() string concatenation function to take an additional argument called SeperatedBy (like in ABAP) which will be used between each string as a seperator. This will lead formatting structures like full name easier. Instead of returning names like "FirstnameLastname", an extended CONCAT() string concatenate function can return "Firstname Lastname", so spaces might be added automatically between non-empty and not null string arguments. I'm planning to request SeperatedBy feature for the SQL Concat() function from SQL teams. (You can vote for this suggesstion at Microsoft Connect)

I hope T-SQL developers will benefit using this new SQL string concatenate function CONCAT() which is first introduced with SQL Server 2012