SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Mass Update generating Dynamic SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sbrazier
Starting Member

19 Posts

Posted - 11/06/2013 :  09:24:00  Show Profile  Reply with Quote
Don't know if what I'm doing is the way to go, any advise is appreciated.

I have a file of address that gets imported from another database. I need to replace patterns in the address field.

example.
Update address set street1 = Replace(street1,pattern,replace_str)

ex: 'WEST ARMISTEAD AVENUE' needs to be updated as 'W ARMISTEAD AVE'

What I have done:
created a table of patterns and replacement strings
created a function to send in the address which returns the replacement string 'REPLACE(REPLACE(street1,'AVENUE','AVE),'WEST','W')

My statement looks like this
UPDATE ADDRESS set Street1 = dbo.F_ReplaceAddrString(street1)

However this does not work
It sets the Street1 = REPLACE(REPLACE(street1,'AVENUE','AVE),'WEST','W')

How can I get the statement [REPLACE(REPLACE(street1,'AVENUE','AVE),'WEST','W')] to execute as a sql statement
and not set it as a string.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/06/2013 :  10:28:27  Show Profile  Reply with Quote
if you've table with patterns and replacementstrings then you require just this

UPDATE t
SET street1 = REPLACE(street1,pattern,replacementstring)
FROM YourTable t
INNER JOIN PatternTable p
ON t.street1 LIKE '% ' + p.pattern + ' %'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sbrazier
Starting Member

19 Posts

Posted - 11/06/2013 :  16:03:18  Show Profile  Reply with Quote
Thanks You!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000