SQLTeam.com | Weblogs | Forums

How to Search and Replace multiple instances in a single update statement

#1

I'm trying to find and replace characters inside a string in one table by using another table as the search/replace dictionary. The update only changes one instance at a time, you can run multiple replaces but that takes too long in my live environment. I've included an example that shows exactly what SQL is doing.

--my main table with varchar text
declare @table as table (string varchar(100))
insert into @table select 'this is a string with several characters'

--a list of find and replace characters
declare @replace as table (find varchar(10), swap varchar(10))
insert into @replace select 'a', 'X'
insert into @replace select 's', 'Y'

select * from @table
select * from @replace

--shows all the matches with each row handling a single replace.
select replace(t.string,r.find,r.swap) as PossibleUpdates
from @table t
inner join @replace r on t.string like '%' + r.find + '%'

--update main table with an update join statement.
update t
set string = replace(t.string,r.find,r.swap)
from @table t
inner join @replace r on t.string like '%' + r.find + '%'

--you can see only one change took affect.
--I need all possible changes to take affect in one update call.
select string as UpdatesMade from @table

Thank you!!

#2

Unfortunately, there is no set-based SQL construct that I know of that allows you to do multiple replace actions as you envision. It would be very useful and nice though, but I don't know of any.

You can do a single replace on multiple rows, or multiple replaces on a single variable (using somewhat non-standard SQL construct). But multiple replaces on a multiple rows can at best be done using nested replaces or loops - which makes it very non-scalable and/or inefficient.

#3

You can nest REPLACEs, and that's what I'd strongly recommend here. You'd need to use dynamic SQL, but that shouldn't be an issue. Note that the cursor is only to construct/concatenate the REPLACEs, not to do the actual UPDATE.

--my main table with varchar text
create table #table ( string varchar(100) )
insert into #table select 'this is a string with several characters'

--a list of find and replace characters
declare @replace as table (find varchar(10), swap varchar(10))
insert into @replace select 'a', 'X'
insert into @replace select 's', 'Y'

select * from #table
select * from @replace

declare cursor_replaces cursor local fast_forward for
select find, swap
from @replace
declare @find varchar(10)
declare @swap varchar(10)
declare @sql varchar(max)

open cursor_replaces

set @sql = 'string,'

while 1 = 1
begin
    fetch next from cursor_replaces into @find, @swap
    if @@FETCH_STATUS <> 0
        if @@FETCH_STATUS = -2
            continue
        else
            break
    set @sql = 'REPLACE(' + @sql + '''' + @find + ''',''' + @swap + '''),'
end --while

set @sql = 'update t set string = ' + stuff(@sql, LEN(@sql), 1, '') + ' from #table t '

select @sql

exec(@sql)

--I need all possible changes to take affect in one update call. 
select string as UpdatesMade from #table
#4

Scott,

Your example is a neat idea and works well with the example I gave. In my live site, I am downloading 1000+ records, and my replace dictionary is a parts table with 2,000 parts (changing ID for Part Name). So the replace SQL would have 2,000 nested replace statements. I'm not sure if that work, but I'll give it a try. Maybe I can do a match, loop through the matches to build the replace, but then the replace statement will be different for each record.

Thank you,
Jason