Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Replace using Select

Posted on 2014-10-30
5
Medium Priority
?
131 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 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

579 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