raterus
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!
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!
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?
For example:
old=A, new=B
old=B, new=C
so that A needs to end up being C?
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>)
e.g.
Update MyTable
Set SomeColumn = Replace(SomeColumn, <ReplacementTableFind>, <ReplacementTableReplace>)
ASKER
no Scott, no cascading values, just simple replacements
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help, works great
Update A
SET A.OldValue = N.NewValue
FROM AnotherTable A
INNER JOIN ReplacementTable N
ON A.OldValue = N.OldValue