Freigeben über

TSQL Tips – Modifying text in rows

  • Artikel

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.

Comments

  • Anonymous
    December 02, 2010
    hi spawar, is better UPDATE Dbo.Table_1 SET c2 = REPLACE(c2,'It','This') WHERE c1 = 1 subquery is not required for this

  • Anonymous
    December 25, 2015
    The comment has been removed