How to update a part of the string using replace function in tsql?

Question

Hi I have a column of nvarchar(1000) type. I need to get rid of encode characters from that column and replace them with their special characters. For Example:

column value is : 'This text values contains this '&' this'.

I have to replace '&' with '&'.

1. First have to find the record which has '&' in the column (may be using like condition)
2. And then replace only this word with its special character

How do i do that? Pl. help

Answer 1

This will replace in the entire column

REPLACE(MyColumn, '&', '&')

You'll have to nest other replacements...

REPLACE(REPLACE(MyColumn, '&', '&'), '>', '>')

All together

UPDATE myTable
SET MyColumn = REPLACE(MyColumn, '&', '&')
WHERE MyColumn LIKE '%&%'

Answer 2

UPDATE mytable
    SET mycol = REPLACE(mycol, N'&', N'&')
    WHERE mycol LIKE '%&%'

EDIT If you decide to replace multiple html entities in one go, the order of the replacements may change results.

For example:

<

becomes &< if you replace first & with & and then < with <, but the result will be < if you first try to replace < with < and then & with &.

If I have to do that kind of replacement, I usually replace & last for this reason. Sure, an edge case, and not something which happens often, but you never know...