<p>What is the best way to replace all '&amp;lt' with <code>&amp;lt;</code> in a given database column? Basically perform <code>s/&amp;lt[^;]/&amp;lt;/gi</code></p>

<p>Notes:</p>

<ul>
<li>must work in <a href="http://en.wikipedia.org/wiki/Microsoft_SQL_Server#SQL_Server_2005" rel="noreferrer">MS SQL Server</a> 2000</li>
<li>Must be repeatable (and not end up with <code>&amp;lt;;;;;;;;;;</code>)</li>
</ul>

Some hacking required but we can do this with
LIKE
,
PATINDEX
,
LEFT
AND
RIGHT
and good old string concatenation.
```
```
create table test
(
    id int identity(1, 1) not null,
    val varchar(25) not null
)

insert into test values ('&lt; <- ok, &lt <- nok')

while 1 = 1
begin
    update test
        set val = left(val, patindex('%&lt[^;]%', val) - 1) +
                      '&lt;' +
                      right(val, len(val) - patindex('%&lt[^;]%', val) - 2)
    from test
    where val like '%&lt[^;]%'

    IF @@ROWCOUNT = 0 BREAK
end

select * from test
```
```
Better is that this is SQL Server version agnostic and should work just fine.