SQL string replace based on WHERE clause

Question

I have a memo field which may contain & and the HTML equivalent &. I need to write a script to ensure that all instances of ampersand are the HTML equivalent. Have the below script but the WHERE clause does not seem to factor the individual instances (strings) of & in the memo field, just the field as a whole... Any ideas on how to accomplish this? Thanks.

 UPDATE 
    STOCKMEM
 SET 
    INETFDESC = CAST(REPLACE(CAST(INETFDESC as NVarchar(MAX)),'&','&amp;') AS NText)
 WHERE 
    INETFDESC LIKE '%&%' 
    AND INETFDESC NOT LIKE '%&amp;%'

What do you mean by "the WHERE clause does not seem to factor the individual instances of &"? Can you show some sample data? — Siyual
– Siyual, Commented Feb 20, 2017 at 16:30
Because there are hundreds of thousands of rows and this is a memo field, looking have the where process only strings which need updating... so only instances where & is proceeded by "amp;" -- does that help? — Justin
– Justin, Commented Feb 20, 2017 at 16:39

Zohar Peled · Accepted Answer · 2017-02-20 17:19:44Z

5

Try this instead:

 UPDATE STOCKMEM
 SET INETFDESC = CAST(
                      REPLACE(
                              REPLACE(
                                      CAST(INETFDESC as NVarchar(MAX))
                              ,'&amp;','&')
                      , '&', ,'&amp;')AS NText)
 WHERE INETFDESC LIKE '%&[^amp;]%'

The first replace will change & to &, and the second will replace all & back to &.

BTW, Please note that NText data type is depricated and you should convert it to nvarchar(max). From MSDN:

IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

edited Feb 20, 2017 at 17:19

answered Feb 20, 2017 at 16:33

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

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

8 Comments

Justin Over a year ago

Perfect, thank you Zohar! With the WHERE LIKE processing all instances of &, not just those needing updating, are there any efficiencies to be gained by adjusting that? This memo field has hundreds of thousands of rows. Thank you.

Justin Over a year ago

Thank you Zohar, look forward to your feedback.

Zohar Peled Over a year ago

I've edited my answer. Now the where clause will not select columns that only have &

Justin Over a year ago

Zohar, this is perfect -- thanks so much for sharing your expertise.

Justin Over a year ago

Hi again Zohar, we ran into an issue where there could potentially be other special HTML characters that begin with the “&” (i.e. — " etc)... so we need to make sure that we don't convert those & to the & otherwise we end up converting something like — to &mdash; ... that said, the longest HTML tag which can start with & seems to be six characters, so &_ _ _ _ _ _; ... do you have any ideas on updating the where clause so that it does not update any & which are proceeded with a ";" in the next 1-7 characters after the &? Thanks a bunch.

|

DVT · Accepted Answer · 2017-02-20 16:34:13Z

2

You can accomplish the same thing by this:

 UPDATE STOCKMEM
 SET INETFDESC = CONVERT(NVARCHAR(MAX),(REPLACE(REPLACE(INETFDESC, '&amp;', '&'), '&', '&amp;')))
WHERE INETFDESC LIKE '%&%'

answered Feb 20, 2017 at 16:34

DVT

3,1274 gold badges16 silver badges25 bronze badges

Collectives™ on Stack Overflow

SQL string replace based on WHERE clause

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related