T-SQL: REPLACE and STUFF Function
Excerpt by Don Kiely
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,
The following SELECT replaces any occurrences of the string '12' with the string 'twelve' in the QuantityPerUnit column of the Northwind Products table:
REPLACE(QuantityPerUnit, '12 ', 'twelve ') AS Twelve
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)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:
Notice that the text that is replaced does not have to be the same length as the replacement string.
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