Please start any new threads on our new site at https://forums.sqlteam.com. 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 

sbrazier
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.

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

52326 Posts

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

- Advertisement -