Perform regex (replace) in an SQL query

create table test
(
    id int identity(1, 1) not null,
    val varchar(25) not null
)

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

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

    IF @@ROWCOUNT = 0 BREAK
end

select * from test

create table test
(
    id int identity(1, 1) not null,
    val varchar(25) not null
)

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

WHILE 1 = 1
BEGIN
    UPDATE test SET
        val = STUFF( val , PATINDEX('%&lt[^;]%', val) + 3 , 0 , ';' )
    FROM test
    WHERE val LIKE '%&lt[^;]%'

    IF @@ROWCOUNT = 0 BREAK
END

select * from test

s/&lt(?!;)/</gi

UPDATE tableName
    SET columName = REPLACE(columName , '&lt', '<')
    WHERE columnName LIKE '%lt%'
    AND columnName NOT LIKE '%lt;%'

UPDATE tableName
    SET columName = REPLACE(columName , '<;', '<')