Solved

Replace using Select

Posted on 2014-10-30
5
125 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 32

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 32

Expert Comment

by:awking00
ID: 40415586
Glad to help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

840 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