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.
All good stuff, but if you have to do a lot of string manipulation, do it in code (SQLCLR for example). TSQL is orders of magnitude slower. (I know Greg knows this, just wanted to mention in case someone is not aware of it.)
Oh yes, but so many don't have SQLCLR enabled. If it's an option, and you're doing a lot of manipulation, it's way faster. But these built-in functions are better than rolling your own in T-SQL 🙂