?
Solved

Replace using Select

Posted on 2014-10-30
5
Medium Priority
?
129 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

762 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