Emailid Split in sql server

Posted by Gopal_nivas under Sql Server on 10/6/2011 | Points: 10 | Views : 2166 | Status : [Member] | Replies : 5
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response