How to update a part of the string using replace f

问题:

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

回答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 '%&%'

回答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...

回答3:

Generic syntax:

UPDATE Table_Name 
SET Column_Name = REPLACE(Column_Name, 'Text_To_Be_Replaced', 'New_Text')
WHERE Column_Name LIKE '%Text_To_Be_Replaced%'

回答4:

Update TABLE
Set    field = replace(field, '&', '&');