Need help with Select statement (Extract Email).

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with Select statement (Extract Email).

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-12-22 : 21:32:18

I need to extract only the first part of email address of the entire string. Sample
data and desire results are below. Thank you in advance for your help.

IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
VendId INT NULL,
Email VARCHAR(100) NULL
)
GO

INSERT INTO dbo.#Temp(VendId, Email)
VALUES (1, 'sam.hantosh@gmail.com')

INSERT INTO dbo.#Temp(VendId, Email)
VALUES (2, 'teammerrick6@yahoo.com#mailto:teammerrick6@yahoo.com#')

INSERT INTO dbo.#Temp(VendId, Email)
VALUES (3, '')

INSERT INTO dbo.#Temp(VendId, Email)
VALUES (4, 'jeff@blackhawkadvisors.net#mailto:jeff@blackhawkadvisors.net#')

INSERT INTO dbo.#Temp(VendId, Email)
VALUES (5, 'jDPall3@msn.com#mailto:DPall3@msn.com#')

go

SELECT *
FROM #Temp;
GO

VendId Email
----------- --------------------------------------------------------------
1 sam.hantosh@gmail.com
2 teammerrick6@yahoo.com#mailto:teammerrick6@yahoo.com#
3
4 jeff@blackhawkadvisors.net#mailto:jeff@blackhawkadvisors.net#
5 jDPall3@msn.com#mailto:DPall3@msn.com#

-- Rules: Extract only the first part of the email. Below is show desire results.

SELECT VendId
,Email
,LEFT(Email, CHARINDEX('#', Email))
--,SUBSTRING(Email, 1, CHARINDEX('#', Email) - 1)
--,LEFT(Email, CHARINDEX('#', Email)-1)

FROM #Temp

-- Desire results:

VendId Email
----------- ----------------------
1 sam.hantosh@gmail.com
2 teammerrick6@yahoo.com
3
4 jeff@blackhawkadvisors.net
5 jDPall3@msn.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-22 : 21:43:44

[code]
SELECT VendId
,Email
,LEFT(Email, CHARINDEX('#', Email + '#') - 1)
from #Temp
[/code]

KH
[spoiler]Time is always against us[/spoiler]

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-12-22 : 22:30:26

Thank you so much.

quote:
Originally posted by khtan

SELECT VendId
,Email
,LEFT(Email, CHARINDEX('#', Email + '#') - 1)
from #Temp

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources