Find and Replace a partial field

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 2005 Forums

Replication (2005)

Find and Replace a partial field

Author

Topic

Savannah03
Starting Member

1 Post

Posted - 2012-04-16 : 00:09:46

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-16 : 07:25:43

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources