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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Mass Update generating Dynamic SQL

Author  Topic 

Starting Member

19 Posts

Posted - 2013-11-06 : 09:24:00
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 - 2013-11-06 : 10:28:27
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 - 2013-11-06 : 16:03:18
Thanks You!
Go to Top of Page

- Advertisement -