We help IT Professionals succeed at work.

Replace one column from a table of replacements

222 Views
Last Modified: 2014-05-28
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!
Comment
Watch Question

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
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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?
Top Expert 2005

Author

Commented:
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>)
Top Expert 2005

Author

Commented:
no Scott, no cascading values, just simple replacements
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2005

Author

Commented:
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

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2005

Author

Commented:
Thanks for the help, works great
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.