Need help with Select statement (Extract Email address).

[LN]

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.h...@gmail.com')

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

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

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

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

go

SELECT *
FROM #Temp;
GO

VendId Email
----------- --------------------------------------------------------------
1 sam.h...@gmail.com
2 teamme...@yahoo.com#mailto:teamme...@yahoo.com#
3
4 je...@blackhawkadvisors.net#mailto:je...@blackhawkadvisors.net#
5 jDP...@msn.com#mailto:DPa...@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.h...@gmail.com
2 teamme...@yahoo.com
3
4 je...@blackhawkadvisors.net
5 jDP...@msn.com

[Plamen Ratchev]

Try this:

SELECT VendId,
Email,
CASE WHEN CHARINDEX('#', Email) > 0
THEN LEFT(Email, CHARINDEX('#', Email) - 1)
ELSE EMail
END AS email_extracted
FROM #Temp;

--
Plamen Ratchev
http://www.SQLStudio.com

[LN]

Plamen - Thank you very much.

"Plamen Ratchev" wrote:

> .
>

[amish]

You can try this one also

SELECT VendId

,LEFT(Email, CHARINDEX('#', Email+'#')-1)
--,SUBSTRING(Email, 1, CHARINDEX('#', Email) - 1)

--,LEFT(Email, CHARINDEX('#', Email)-1)

FROM #Temp

Amish Shah
http://shahamishm.blogspot.com