All things Data Related....

On this site I will be posting entries related to Data platform and analytics that I learn and believe could be valuable learning to others who want insight from their data

TSQL Tips – Modifying text in rows

TSQL Tips – Modifying text in rows

  • Comments 1
  • Likes

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
  • hi spawar,

    is better

    UPDATE Dbo.Table_1

    SET c2 = REPLACE(c2,'It','This')

    WHERE c1 = 1

    subquery is not required for this

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment