"Find and Replace" in TSQL query? 1

[SQLHacker]

I am trying to replace values in a table column. It is a single table (although it is related to other tables...) that has a column called "CompanyEmailAddress". One of our sites has changed the domain name, which of course changes the email address. For example, the current email address of everyone at the site is something like "@companyeng.com". I need to change it to "@companytech.com".

I am able to see all of the records that qualify by running the following query:
SELECT CompanyEmailAddress FROM EmployeeTable
WHERE CompanyEmailAddres LIKE '%@companyeng.com';
I get 44 records.
However, when I try to use this criteria in an UPDATE statement, it doesn't work.

I've tried:
UPDATE EmployeeTable
SET CompanyEmailAddress = '%@companytech.com'
WHERE CompanyEmailAddress LIKE '%@companyeng.com'

I get the following error when I execute the above UPDATE statement:
"Msg 203010, Level 16, State 1, Procedure shrRaiseError, Line 31
This company E-mail address already exists as another person's personal or company E-mail address. Please change the company E-mail address.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted."

Can anyone suggest an alternate method (like Find-Replace) that will work in this situation?

Thanks!

 

[bborissov]

Try

[COLOR=blue]UPDATE[/color] EmployeeTable
       [COLOR=blue]SET[/color] CompanyEmailAddress = 
           [COLOR=#FF00FF]REPLACE[/color](CompanyEmailAddress,
                   [COLOR=red]'@companyeng.com'[/color],
                   [COLOR=red]'@companytech.com'[/color])
[COLOR=blue]WHERE[/color] CompanyEmailAddress LIKE [COLOR=red]'%@companyeng.com'[/color]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLHacker]

Thank you very much Borislav!

I knew it couldn't be too hard (just hard enough that I couldn't figure it out).

Thanks again!