Avatar of raterus
raterus
Flag 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!
Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
raterus

8/22/2022 - Mon
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
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?
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>)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
raterus

ASKER
no Scott, no cascading values, just simple replacements
SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
raterus

ASKER
Thanks for the help, works great
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.