SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server 2012 Download and T-SQL Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



SQL Concat String Function in Transact-SQL with SQL Server 2011 Denali

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 2011 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 2011 automatically into a character value.

SELECT CONCAT('SQL Server ', 2011)

SQL Server Concat() string concatenation function example

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

SELECT 'SQL Server ' + 2011

conversion failed when converting the character value to data type int

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

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

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', 2011, NULL, 'Denali')

TSQL Concat function

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

SELECT CONCAT(NULL,NULL)

SQL Server 2011 Concat string function


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')

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

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 2011, Denali CTP3.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems