SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
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  

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.

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.

Very Important crosS Applying yaK Herder

52326 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

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

SQL Server MVP
Go to Top of Page

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.03 seconds. Powered By: Snitz Forums 2000