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 2005 Forums
 Replication (2005)
 Find and Replace a partial field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Savannah03
Starting Member

USA
1 Posts

Posted - 04/16/2012 :  00:09:46  Show Profile  Reply with Quote
I am using the following code to replace certain words within a field. I would like to nest this code so that I can look for more than one variation of the word, example "ST", "ST." or "STR."

UPDATE tableName
SET fieldName = REPLACE(fieldName, " st ", " street ")
WHERE fieldName LIKE '* st *

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/16/2012 :  07:25:43  Show Profile  Reply with Quote
What you really need is a regular expression, which unfortunately, T-SQL does not support natively. But, like you were thinking, you can nest the replace functions like this:
UPDATE tableName
SET fieldName = 
	REPLACE(REPLACE(REPLACE(fieldName, ' St. ', ' street '),' St ',' street'),' Str. ','street')
WHERE fieldName LIKE '% st %'
Not very elegant, and perhaps error-prone, and certainly does not scale.

If you need to do a lot of this type of thing, you might want to check out regular expressions implemented via CLR stored procs. There is a description and downloadable code on this page: http://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/
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.08 seconds. Powered By: Snitz Forums 2000