Solved

Replace using Select

Posted on 2014-10-30
5
119 Views
Last Modified: 2014-10-31
I have a table called REPLACE_ITEMS.  It has two columns called old_value, new_value
I would like to write a replace statement to cross reference the values from the table
for example

in a table called ADDRESS I have a column called addr_1
I would like to run the replace statement against addr_1 cross referencing all possibilities from the REPLACE_ITEMS table.

Here is a scenario of what something would look like in the address table before and after:
The replace query would find "rd" in the replace_items.old_value and replace it with the replace_items.new_value of "Road"

OLD .............  4101 test rd
NEW.............. 4101 test Road

the question: How would the replace update query be written?
Thanks in Advance!
0
Comment
Question by:H-SC
  • 2
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40413621
This is Data Cleansing, and the best way to do it is using the Data Quality Services (DQS), which is part of SQL Server 2012 and 2014.

Otherwise, you are doing a cursor, opening the replace_items and then updating addr_1 wherever it appears (you can test for that using the Replace function).
0
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
ID: 40414046
This assumes addressid as a primary key for the address table -
merge address as a
using
(select a. addressid, replace(a.addr1, r.old_value, r.new_value) chgValue
 from replace_items r,
 address a
 where charindex(r.old_value, a.addr1) > 0) as x
on (a.addressid = x.addressid)
when matched then
update set a.addr1 = x.chgValue;
0
 
LVL 1

Author Comment

by:H-SC
ID: 40414231
Phillip,
Many thanks for explaining the DQS option, I was aware that newer versions had this feature, but was looking for more of a query approach.
0
 
LVL 1

Author Comment

by:H-SC
ID: 40414233
awking00,
Your solution works great!  Many thanks
0
 
LVL 31

Expert Comment

by:awking00
ID: 40415586
Glad to help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now