MSSQL Change Text within Column

Needing to change or replace a text, a word or phrase, within a VARCHAR column is very easy using the replace function.

This example changes every occurrence of the year "2018" with "2019". We are updating answers to the next year in an FAQ table.

First, how do you identify which columns contain "2018" or our search phrase?

view plain print about
1SELECT [Question]
2 ,[Answer]
3 FROM [FAQ]
4 WHERE [Answer] LIKE '%2018%'

Next we change or replace every occurrence of our search phrase with our new phrase or text.

view plain print about
1UPDATE [FAQ]
2 SET [Answer] = REPLACE([Answer], '2018', '2019')
3 WHERE [Answer] LIKE '%2018%'

How easy is that?

Just a reminder that this specific SQL is for VARCHAR only. TEXT and NTEXT fields will have to be CAST to VARCHAR(MAX) and could cause truncation.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Blog Owner