Emailid Split in sql server

Posted by Gopal_nivas under Sql Server on 10/6/2011 | Points: 10 | Views : 3311 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi..

i have a mailid like this sample@gmail.com
in my table i want to insert two values like name and emailid
for name i want to take the text in in mailid. i.e) before @ sysmbol i want to take tat text and inserted into my table.

how to do this..

need ur suggetsions with examples
regards
gopal.s

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Blessyjees on: 10/6/2011 [Member] Bronze | Points: 25

0	Hi, By using SUBSTRING('sample@gmail.com',1,charindex('@','sample@gmail.com',1)-1) you can split name ( the text before @) from emailid select SUBSTRING('sample@gmail.com',1,charindex('@','sample@gmail.com',1)-1) Blessy Baby Digitalmesh Softech pvt Ltd https://blessybaby.wordpress.com/ Gopal_nivas, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Gopal_nivas on: 10/6/2011 [Member] Starter | Points: 25

0	thanks blessyjees.. its works well . reagards gopal.s Gopal_nivas, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Blessyjees on: 10/7/2011 [Member] Bronze | Points: 25

0	Hi Gopal, Please set this reply to "Mark As Answer". Blessy Baby Digitalmesh Softech pvt Ltd https://blessybaby.wordpress.com/ Gopal_nivas, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: PandianS on: 10/7/2011 [Member] [MVP] Silver | Points: 25

0	Hi Gopal Kindly make as "Mark as Answer" once you got the appropriate/correct answer Immediately :) Otherwise, You will get lot of replies. May be some of those wrong. You will get confuse. :) Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Gopal_nivas, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Niladri.biswas on: 10/9/2011 [Member] Platinum | Points: 25

0	Hi, you can even try with the following too Declare @str varchar(50) = 'sample@gmail.com' Solution 1 Select Name = Left(@str, PATINDEX('%@%',@str)-1) ,Domain = Right(@str,Len(@str)-PATINDEX('%@%',@str)) ,Email = @str Solution 2 Select Name = Left(@str, CHARINDEX('@',@str)-1) ,Domain = Right(@str,Len(@str)-CHARINDEX('@',@str)) ,Email = @str Solution 3 Select Name = REVERSE(STUFF(REVERSE(@str),1,PATINDEX('%@%',REVERSE(@str)),'')) ,Domain = STUFF(@str,1,PATINDEX('%@%',@str),'') ,Email = @str In all the three cases the answer will be Name Domain Email sample gmail.com sample@gmail.com Hope this helps Best Regards, Niladri Biswas Gopal_nivas, if this helps please login to Mark As Answer. \| Alert Moderator

Hi, you can even try with the following too


Declare @str varchar(50) = 'sample@gmail.com'

Solution 1


Select 

		Name  = Left(@str, PATINDEX('%@%',@str)-1)

		,Domain = Right(@str,Len(@str)-PATINDEX('%@%',@str))

		,Email = @str

Solution 2


Select 

		Name  = Left(@str, CHARINDEX('@',@str)-1)

		,Domain = Right(@str,Len(@str)-CHARINDEX('@',@str))

		,Email = @str

Solution 3


Select 

		Name  = REVERSE(STUFF(REVERSE(@str),1,PATINDEX('%@%',REVERSE(@str)),''))

		,Domain = STUFF(@str,1,PATINDEX('%@%',@str),'')

		,Email = @str

In all the three cases the answer will be


Name	Domain	Email

sample	gmail.com	sample@gmail.com

Hope this helps

Best Regards,
Niladri Biswas

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts