Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.08 seconds. Powered By: Snitz Forums 2000