T-SQL 101: 48 Replacing characters in strings in SQL Server using REPLACE and STUFF

I often need to replace characters in one string with other characters. There are three basic functions in T-SQL for doing that. The most common two functions for this are REPLACE and STUFF.

REPLACE is a simple function. You tell it which string to work on, the substring to find, and which string to replace it with. You can see it here:

I asked it to replace all occurrences of DEF with HELLO. Note that the replacement doesn’t need to be the same length as what it’s replacing. A very common use of REPLACE is to remove characters by making the replacement string just a blank string.

The other function is STUFF. It is a bit similar but instead of telling it which substring to find, you tell it where the characters that you want to replace are. Check this example:

In this case, we’ve asked STUFF to remove 6 characters starting at position 3, and insert ZZ instead.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-12-16