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!!