find replace text in sql 2005 database

colonialiu20
colonialiu20 used Ask the Experts™
on
I have a db that I need to find /replace some email addresses.  In my example, lets say the db name is db1, the name of the table is dbo.people, and the field is email.  I need to change all email addresses that contain @example.com to @ test.org.

What type of sql statement do I have to make to make this update?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator
Top Expert 2005
Commented:
UPDATE db1.dbo.people
SET email = REPLACE(email, '@example.com', '@test.org')
WHERE email LIKE '%@example.com'
Vikas GargAssociate Principal Engineer
Top Expert 2014
Commented:
Hi,

Please try this code

UPDATE db1.dbo.people
SET email = STUFF(email, CHARINDEX('@example.com',email , 1), LEN('@example.com'), '@test.org')
WHERE email LIKE '%@example.com' 

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today