How to replace the strings '&' for '&&' in a text field

Question

This is for SQL Server 2000 and I can't change that :(

I'm having some problems searching for the string

&

and replacing it with

&&

I'm targeting a text field (not full-text indexed)

I have tried with

UPDATE documents
SET doc_xml = REPLACE(doc_xml, '&' , '&&')

but this is not working as I have to escape the & char. I'd tried with

ESCAPE '\'

at the end, but no luck.

An example target content is this:

<FOLDER_Name>Buy&amp;&amp;Go</FOLDER_Name>
<PRODUCT_Name>Buy &amp;&amp; Go</PRODUCT_Name>
<SKU_Name>Buy &amp; Go</SKU_Name>
<COMPANY_Name>AT&amp;T</COMPANY_Name>   

I want to replace only the single

 & 

in those last two lines, and the result I need to obtain is this one:

<FOLDER_Name>Buy&amp;&amp;Go</FOLDER_Name>
<Product_Name>Buy &amp;&amp; Go</Product_Name>
<SKU_Name>Buy &amp;&amp; Go</SKU_Name>
<COMPANY_Name>AT&amp;&amp;T</COMPANY_Name>

I have to keep spaces as in the original content and also don't modify the strings that already have a double ampersand

&&  

Answer 1

One way would be to do your replace twice, with the second one to fix the quad values.

declare @table table (x xml)
insert into @table
values( 
'<FOLDER_Name>Buy&amp;&amp;Go</FOLDER_Name>
<PRODUCT_Name>Buy &amp;&amp; Go</PRODUCT_Name>
<SKU_Name>Buy &amp; Go</SKU_Name>
<COMPANY_Name>AT&amp;T</COMPANY_Name>')

update @table
set x = convert(xml,
                replace(
                    replace(convert(varchar(max),x),'&amp;','&amp;&amp;'),'&amp;&amp;&amp;&amp;','&amp;&amp;'
                    )

                )

select * from @table

If this isn't an xml column then:

declare @table table (x varchar(max))
insert into @table
values( 
'<FOLDER_Name>Buy&amp;&amp;Go</FOLDER_Name>
<PRODUCT_Name>Buy &amp;&amp; Go</PRODUCT_Name>
<SKU_Name>Buy &amp; Go</SKU_Name>
<COMPANY_Name>AT&amp;T</COMPANY_Name>')

update @table
set x =     replace(
                replace(x,'&amp;','&amp;&amp;'),'&amp;&amp;&amp;&amp;','&amp;&amp;'
                )

select * from @table

So your code would be something like:

UPDATE documents
SET doc_xml = 
    convert(xml,
                replace(
                    replace(convert(varchar(max),x),'&','&&'),'&&&&','&&'
                    )

                )