Parsing out email address 1

[IT4EVR]

What I'm trying to do is parse out a first and last name from an email address, separating them into first and last name fields in a view. I'm like 99% there but can't figure out what I am doing wrong.

The Last Name fields displays perfectly but the First Name displays as .John or .Dave. I know that represents one character off but if I try to subtract -1 from the second CHARINDEX I get an error.

A sample email address is John.Smith@anything.com.

SELECT     Email, SUBSTRING(Email, 0, CHARINDEX('.', Email)) AS [Last Name], SUBSTRING(Email, CHARINDEX('.', Email), CHARINDEX('@', Email) 
                      - CHARINDEX('.', Email)) AS [First Name]
FROM         dbo.tblUndeliverableEmails

 

[SQLDenis]

If the format is always firstname.lastname@something.com

then try this

select Email,parsename(Email,3) as FirstName,left(parsename(Email,2),(CHARINDEX('@', Email) -LEN(parsename(Email,3)))-2) as LastName
FROM dbo.tblUndeliverableEmails

example
-------------
declare @v varchar(500)
select @v ='John.Smith@anything.com'

select parsename(@v,3) as FirstName,left(parsename(@v,2),(CHARINDEX('@', @v) -LEN(parsename(@v,3)))-2) as LastName





Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

you can also put a replace('.','') around your solution

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SBTBILL]

Watch out for John.SmithWeston or Mary.Ann.McGillicutty

Bill Couture

 

[DocuMan]

I always use REVERSE when I'm looking for the last instance of a character in a string.

 

[IT4EVR]

Thanks, Denis, I wasn't aware of the parsename function. It comes in very handy.

 

[IT4EVR]

I also got my original sql statement to work:

SELECT     Email, SUBSTRING(Email, 0, CHARINDEX('.', Email)) AS [Last Name], SUBSTRING(Email, CHARINDEX('.', Email) + 1, CHARINDEX('@', Email) 
                      - CHARINDEX('.', Email) - 1) AS [First Name]
FROM         dbo.tblUndeliverableEmails