Solved

Replace using Select

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

695 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