Want to replace email ID's - SQL Server Forums

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

Want to replace email ID's

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-05-09 : 11:09:59

Hi all,

I have different email id i have to replace all mailing address with a different mail id
If i have different mail ids like this

ahtygh@gmail.com
nuhjij@yahoo.com
hiuiojo@hotmail.com
hhjj@yahoo.in
ttty@gmail.in

i have to replace all the mail like this

ahtygh@cool.com
nuhjij@cool.com
hiuiojo@cool.com
hhjj@cool.com
ttty@cool.com

HOw i can do that....

Regards,
Divya

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-09 : 11:59:23

Try this:


SELECT  STUFF(T.email, T.atIndex + 1, CHARINDEX('.', T.email, T.atIndex) - T.atIndex - 1, 'cool')
  FROM (SELECT E.email, CHARINDEX('@', E.email) AS atIndex
          FROM (SELECT 'ahtygh@gmail.com' AS email UNION ALL
                SELECT 'nuhjij@yahoo.com') AS E) T

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-09 : 17:28:18

Also

;WITH emails(email) AS
(SELECT 'ahtygh@gmail.com' AS email UNION ALL
SELECT 'nuhjij@yahoo.com')
SELECT SUBSTRING(email, 1, CHARINDEX('@', email))
       +'cool'
       +SUBSTRING(email, LEN(email)-CHARINDEX('.',REVERSE(email))+1, CHARINDEX('.',REVERSE(email)))      
FROM emails

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-10 : 02:39:45

Or this

UPDATE Email SET EmaiID = SUBSTRING( EmailID, 1, CHARINDEX('@', EmailID) ) + 'cool.com'

OR

Update Email SET EmailID = LEFT(EmailID,CHARINDEX('@',EmailID)) + 'cool.com'

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-10 : 03:18:36

Yet another STUFF

UPDATE Email SET EmaiID = stuff(EmaiID , CHARINDEX('@',EmaiID), len(EmaiID),'@cool.com')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

divyaram
Posting Yak Master

180 Posts

Posted - 2010-05-10 : 06:56:31

quote:
Originally posted by senthil_nagore

Yet another STUFF

UPDATE Email SET EmaiID = stuff(EmaiID , CHARINDEX('@',EmaiID), len(EmaiID),'@cool.com')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

thank you all.....

Regards,
Divya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources