LearnNowOnline

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.

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)

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.

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