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 '&'.
- First have to find the record which has
'&'
in the column (may be using like condition) - 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 &
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...
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, '&', '&');
精彩评论