Perform regex (replace) in an SQL query

alumb picture alumb · Sep 30, 2008 · Viewed 96.8k times · Source

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

Notes:

  • must work in MS SQL Server 2000
  • Must be repeatable (and not end up with <;;;;;;;;;)

Answer

Jorge Ferreira picture Jorge Ferreira · Sep 30, 2008

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.