We often need to combine two or more string values to use this combined string value in reporting. Although there was a way to do that in earlier versions of SQL Server, starting with SQL Server 2012 we have CONCAT function for this specific situation. This T-SQL function also takes care of data type conversion and handling NULLs appropriately. Apart from that, there has been a STUFF T-SQL function in SQL Server, which you can use to insert\replace a string value into another string value. The difference lies in the fact that CONCAT allows you to append a string value at the end of another string value whereas STUFF allows you insert or replace a string value into or in between another string value. I am going to demonstrate these functions and their real life usages in this article.
Prior to SQL Server 2012, we used to use the “+” operator to combine or concatenate two or more string values but starting with SQL Server 2012, we can use CONCAT function to perform this type of operation more neatly. For example, the below script and screenshot shows usage of the “+” operator for string values concatenation and its result.
SELECT 'One' + ',' + 'Two' + ',' + 'Three'
SELECT FirstName + ',' + LastName FROM [Person].[Person]
String value concatenation and its result
There are a couple of specific behaviors with the usage of “+” operator; for example,
- “+” operator is an overloaded operator, it performs summation if the passed values are numeric or it does concatenation if the passed values are string. If you pass values with both data types together it fails as it does not know if it has to do summation or concatenation.
- If any of the results or values are NULL, the resultant value will be NULL too.
The script below demonstrates the usage of the “+” operator with values of different data types and how it fails with an exception as it does not know if it has to do summation or concatenation. It tries to do summation (even if you intend to do concatenation of string values) but as string values cannot be converted to numeric it fails with a message of conversion.
SELECT 'One' + ',' + 'Two' + ',' + 'Three' + ',' + 12345
SELECT BusinessEntityID + ',' + FirstName + ',' + LastName FROM [Person].[Person]
CONCAT is a new T-SQL function, which accepts two or more parameter values separated by comma, introduced in SQL Server 2012. All passed-in parameter values are concatenated to a single string and are returned back.
The script below demonstrates the usage of the CONCAT function to concatenate two or more string values. As the CONCAT function has been designed for string concatenation, it converts all the non-string input values to string data type and performs concatenation on that.
SELECT CONCAT('One', ',', 'Two', ',', 'Three', ',', 12345)
SELECT CONCAT(BusinessEntityID, ',', FirstName, ',', LastName) FROM [Person].[Person]
CONCAT function results
The CONCAT function handles NULL on its own. You don't need to use the ISNULL function with each of the values to handle NULL explicitly as we do in case of the "+" operator.
The script and screenshot below demonstrate NULL handling behavior with the “+” operator as well as the CONCAT function. As you can notice, unlike the “+” operator, CONCAT ignores the NULL values (in actuality it replaces NULL with an empty string) and returns a concatenated string with rest other passed-in values.
SELECT 'One' + ',' + 'Two' + ',' + 'Three' + ',' + NULL + 12345
SELECT CONCAT('One', ',', 'Two', ',', 'Three', ',', NULL, 12345)
We looked at using the CONCAT function to concatenate or combine two or more string values. With this function, string values are appended at the end of the prior string but there are times when you want to insert a string value into another string value or replace a part of one string with another string. This is where you can leverage the STUFF T-SQL function.
Unlike the CONCAT function, which was introduced in SQL Server 2012, the STUFF function has been in prior versions of SQL Server too and hence you can use it in them too.
Based on your requirement, you can either insert a second string into the main string or replace a part of the main string with a second string. In that case it deletes a specified length of the characters in the main string at the specified start position and then inserts the second string into the main string at the specified start position. Here is the complete syntax of using the STUFF function:
STUFF (<main string>, <start position to insert of replace>, <number of character to replace; specify 0 in case of insertion>, <second string to either insert or replace>)
The first select statement in the script below demonstrates how it inserts a second string into the main string at the specified start position whereas the second select statement replaces part of the main string with the second string.
SELECT STUFF('One match', 5, 0, 'day ')
SELECT STUFF('One match', 5, 4, 'day ')
STUFF function results
In this article, I discussed two important string functions, CONCAT and STUFF. CONCAT allows you to append a string value at the end of another string value whereas STUFF allows you insert into or in between another string value (or replace a part of the main string value).
CONCAT T-SQL Function
STUFF T-SQL Function
See all articles by Arshad Ali