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 '&amp;' 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

gbn · Accepted Answer · 2011-01-31 21:16:41Z

32

This will replace in the entire column

REPLACE(MyColumn, '&amp;', '&')

You'll have to nest other replacements...

REPLACE(REPLACE(MyColumn, '&amp;', '&'), '&gt;', '>')

All together

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

edited Jan 31, 2011 at 21:16

answered Jan 31, 2011 at 21:07

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gbn Over a year ago

@Jango: sorry, was rushing and only saw the string replace issue

User13839404 Over a year ago

@gb: Thanks but i think it won't work w/o mentioning column name after SET or will it?

gbn Over a year ago

@Jango: sorry again. I'm typing on a phone. Not very well either

marapet · Accepted Answer · 2011-01-31 21:20:34Z

8

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

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

For example:

&amp;lt;

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

edited Jan 31, 2011 at 21:20

answered Jan 31, 2011 at 21:09

marapet

56.9k13 gold badges176 silver badges186 bronze badges

1 Comment

User13839404 Over a year ago

Actually, i am doing multiple replacements. You have a point. Thanks.

Michael Petrotta · Accepted Answer · 2013-09-03 09:12:04Z

5

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

edited Sep 3, 2013 at 9:12

Michael Petrotta

61.1k27 gold badges153 silver badges181 bronze badges

answered Nov 8, 2012 at 7:13

Nalan Madheswaran

10.6k1 gold badge60 silver badges44 bronze badges

Comments

PMC · Accepted Answer · 2011-01-31 21:08:48Z

1

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

answered Jan 31, 2011 at 21:08

PMC

4,7563 gold badges39 silver badges59 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related