Link to home
Start Free TrialLog in
Avatar of raterus
raterusFlag for United States of America

asked on

Replace one column from a table of replacements

Hey there,

I have a table of replacements (oldValue, newValue)

I'd like to perform these replacements on a column in another table.  I need to use the SQL Replace() function in an update statement.

Any ideas without resorting to a cursor?

Thanks!
Avatar of dannygonzalez09
dannygonzalez09

you can use a update statement with a join on both the tables.. Something like below

Update A
SET A.OldValue = N.NewValue
FROM AnotherTable A
INNER JOIN ReplacementTable N
ON A.OldValue = N.OldValue
Avatar of Scott Pletcher
Do you allow / are there any cascading old/new values?

For example:
old=A, new=B
old=B, new=C
so that A needs to end up being C?
Avatar of raterus

ASKER

No no, I need to use the sql Replace() function, as I stated in the original question.

e.g.

Update MyTable
Set SomeColumn = Replace(SomeColumn, <ReplacementTableFind>, <ReplacementTableReplace>)
Avatar of raterus

ASKER

no Scott, no cascading values, just simple replacements
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of raterus

ASKER

Eww, multiple replacements!  yes, I have to support that.  Given the following sql, I'd need 'bbdd' as the output.

declare @replacements table
(
	oldvalue varchar(10),
	newvalue varchar(10)
)

insert into @replacements (oldvalue, newvalue) values ('a', 'b'), ('c', 'd')

declare @main table
(
	col varchar(100)
)

insert into @main (col) values ('abcd')

select *
from @main

update m
set m.col = replace(m.col, r.oldvalue, r.newvalue)
from @main m
	inner join @replacements r on m.col like '%' + r.oldvalue + '%'
	
select *
from @main

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of raterus

ASKER

Thanks for the help, works great