New to SQL need help updating email address

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

General SQL Server Forums

New to SQL Server Programming

New to SQL need help updating email address

Author

Topic

essolinux
Starting Member

1 Post

Posted - 2010-07-02 : 14:33:25

I need help updating a part of the email address.
We had a Domain Name change and I need to change the domain part of the email for all users in our SQL DB.

We went from something like esso@testsoft.com to esso@test.com

Does anyone know how I could update just the domain part?
What would be a command?

Thanks

Mike

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-02 : 14:55:13

something like this will work for something like the example you provided, but won't for the something not like the sample you provided, you may have to supply different values in the replace statement to cover all the possibilities

declare @mail varchar(50)
set @mail = 'esso@testsoft.com'

select replace(@mail,'@testsoft.com','@soft.com')

Jim

Everyday I learn something that somebody else already knew

dattatreysindol
Starting Member

20 Posts

Posted - 2010-07-02 : 15:04:11

Hi There -

Well there are multiple ways of implementing this. Here is another way of doing it:

UPDATE TableName
SET Email = STUFF(Email,(CHARINDEX('@',Email,1) + 1),(CHARINDEX('.com',Email,1) - CHARINDEX('@',Email,1) - 1),'test')
WHERE Email LIKE '%@testsoft.com%'

Hope that helps!

Dattatrey Sindol
http://mytechnobook.blogspot.com/

This information is provided "AS IS" with no warranties, and confers no rights.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-07-03 : 02:57:51

quote:
Originally posted by jimf

something like this will work for something like the example you provided, but won't for the something not like the sample you provided, you may have to supply different values in the replace statement to cover all the possibilities

declare @mail varchar(50)
set @mail = 'esso@testsoft.com'

select replace(@mail,'@testsoft.com','@soft.com')

Jim

Everyday I learn something that somebody else already knew

But to be more generalize you can use this -

declare @mail varchar(50)
declare @Domain varchar(50)
set @mail = 'esso@testsoft.com'
SET @Domain = 'test.com'

select replace(@mail,'@testsoft.com','@soft.com')

SELECT LEFT( @mail, CHARINDEX('@',@mail)-1) + '@' + @Domain

--to update values in tables

UPDATE TableName SET Email = LEFT( Email, CHARINDEX('@',Email)-1) + '@' + @Domain

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources