Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted April 20, 2015

CONCAT and STUFF Functions in SQL Server 2012

By Arshad Ali

Introduction

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.

CONCAT Function

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'
GO
SELECT   FirstName + ','   + LastName FROM   [Person].[Person]
GO

String value concatenation and its result
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
GO
SELECT    BusinessEntityID + ','   + FirstName +   ',' + LastName   FROM [Person].[Person]
GO

Result message
Result message

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)
GO
SELECT CONCAT(BusinessEntityID, ',', FirstName, ',', LastName) FROM [Person].[Person]
GO

CONCAT function results
CONCAT function results

NULL Handling

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
GO
SELECT CONCAT('One', ',', 'Two', ',', 'Three', ',', NULL, 12345)
GO

NULL results
NULL results

STUFF Function

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 ')
GO
SELECT STUFF('One match', 5, 4, 'day ')
GO

STUFF function results
STUFF function results

Conclusion

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

Resources

CONCAT T-SQL Function

STUFF T-SQL Function

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM