TSQL Tips – Modifying text in rows

I have been doing some loading of random text in tables, but wanted to make changes to some of the text in rows, instead of running a full update operation, I discovered that you can also use TSQL functions REPLACE or STUFF to change text.

Below are examples:

UPDATE Dbo.Table_1
SET c2 = (SELECT REPLACE(c2,'It','This'))
WHERE c1 = 1

 

UPDATE dbo.Table_1
SET c2 = (SELECT STUFF(c2,5, 8, 'Add this text'))
WHERE c1=2

REPLACE - this function replaces all the instances of a specified source string within a target string. The first parameter is the source string expression, next is the search string, and last is the replacement string.

STUFF - inserts one string in another. The first parameter is the source string expression. Next is the insertion point, then the number of characters to delete, and finally the string to be inserted.