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:

Buy&&Go
Buy && Go
Buy & Go
AT&T

I want to replace only the single

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

Buy&&Go
Buy && Go
Buy && Go
AT&&T

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

&&

user_6167855 · Answer

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( 
'Buy&&Go
Buy && Go
Buy & Go
AT&T')

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

                )

select * from @table

If this isn't an xml column then:

declare @table table (x varchar(max))
insert into @table
values( 
'Buy&&Go
Buy && Go
Buy & Go
AT&T')

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

select * from @table

So your code would be something like:

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

                )

user_10699932 · Answer

Maybe this could work on SQL2000:

UPDATE documents
SET doc_xml =
REPLACE(
    REPLACE(
        REPLACE(CAST(doc_xml as varchar(8000))
            ,'&&', '&&'
                )
            ,'&', '&&'
            )
            ,'&&', '&&'
        )

You can try on your SSMS:

DECLARE @doc_xml varchar(4000)
SET @doc_xml =
'Buy&&Go
Buy && Go
Buy & Go
AT&T'

PRINT
REPLACE(
    REPLACE(
        REPLACE(@doc_xml
            ,'&&', '&&'
                )
            ,'&', '&&'
            )
            ,'&&', '&&'
        )

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

2 Answers

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