Blog

T-SQL: REPLACE and STUFF Function

Excerpt by Don Kiely | April 05, 2013

All programming languages include functions for manipulating strings. This article shows how to use REPLACE and STUFF, some of the most common ones in Transact-SQL. String manipulation is not one of T-SQL's strongest areas, but you can still perform some complex operations by using these functions, particularly if you are creative and use various functions together

REPLACE and STUFF Functions

REPLACE replaces all occurrences of a specific string with another, using the following syntax. string_expression specifies the string you want to search, string_pattern what you want to search for, and string_replacement what you want to replace each match with.

REPLACE ( string_expression, string_pattern,
string_replacement )

The following SELECT replaces any occurrences of the string '12' with the string 'twelve' in the QuantityPerUnit column of the Northwind Products table:

SELECT QuantityPerUnit,
REPLACE(QuantityPerUnit, '12 ', 'twelve ') AS Twelve
FROM dbo.Products;

What's especially handy about REPLACE is that it can replace multiple instances of the target string with one call, as shown in several of the rows in the Figure below.

The REPLACE function

TIP: You can use REPLACE to create expressions in UPDATE statements that modify char, nchar, varchar, and nvarchar values. However, you cannot use REPLACE for text, ntext, or image data. In previous versions of SQL Server, you needed to use UPDATETEXT, and that is still supported but is deprecated. However, if you are using the new varchar(max), nvarchar(max), or binary(max) data types, you can use the .WRITE clause in an UPDATE statement.

STUFF also replaces one section of a string with another, but does so based on the starting position and length of the section to replace, as shown in the following syntax:

STUFF (expr1, start, length, expr2)
The following example performs surgery on the string '12345'. It starts at the third character, removes two characters, and inserts the string 'xxxx' in place of those two characters:
SELECT STUFF('123456', 3, 2, 'xxxx');

Here are the results:

12xxxx56

Notice that the text that is replaced does not have to be the same length as the replacement string.

Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.

 



Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.


This course excerpt was originally posted April 05, 2013 from the online courseware SQL Server 2012, Part 7 of 9: Introduction to T-SQL by Don Kiely