开发者

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

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


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


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 &amp; with & and then &lt; with <, but the result will be &lt; if you first try to replace &lt; with < and then &amp; with &.

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


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%'


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜