March 12, 2015 at 11:34 am
I'm trying to strip out the First & Last Name from an Email Address. The email address is formatted as "FirstName.LastName@emailaddress.com" but sometimes a middle initial is used (ex: "First.M.Last@emailaddress.com").
I can locate the '@' sign and the first '.' period(from left to right) but need to find the first '.' from the left of the '@' sign.
FYI - I'm doing this in a view.
Here's some sample data and what I've tried:
CREATE TABLE dbo.Table1
(EmailAddr VARCHAR(255))
INSERT INTO dbo.Table1 (EmailAddr)
SELECT 'Andy.Smith@gmaddr.com'
UNION ALL
SELECT 'Betty.Lee-Jones@gmaddr.com'
UNION ALL
SELECT 'Tim.F.Doe@gmaddr.com'
UNION ALL
SELECT 'Jim.George@gmaddr.com'
UNION ALL
SELECT 'This.N.That-Right@gmaddr.com'
GO
select
EmailAddr
,LEFT(EmailAddr, CHARINDEX('.', EmailAddr) -1) as FirstName
,LEFT(EmailAddr, (CHARINDEX('@', EmailAddr,1)-1)) as FullName
,(CHARINDEX('@', EmailAddr,1)-0) as AtSignLoc
,(CHARINDEX('.', EmailAddr,1)+1) as [FirstPeriod&oneLoc]
,((CHARINDEX('@', EmailAddr,1)-0) - (CHARINDEX('.', EmailAddr,1)+1)) as FieldLength
,SUBSTRING(EmailAddr,(CHARINDEX('.', EmailAddr,1)+1),((CHARINDEX('@', EmailAddr,1)-0) - (CHARINDEX('.', EmailAddr,1)+1))) as LastName
from Table1
Results of the above query:
EmailAddrFirstNameFullNameAtSignLocFirstPeriod&oneLocFieldLengthLastName
Andy.Smith@gmaddr.comAndyAndy.Smith1165Smith
Betty.Lee-Jones@gmaddr.comBettyBetty.Lee-Jones1679Lee-Jones
Tim.F.Doe@gmaddr.comTimTim.F.Doe1055F.Doe
Jim.George@gmaddr.comJimJim.George1156George
This.N.That-Right@gmaddr.comThisThis.N.That-Right18612N.That-Right
What I want is:
LastName
Smith
Lee-Jones
Doe
George
That-Right
Thanks,
Sqlraider
March 12, 2015 at 12:01 pm
This is an option. I'm using CROSS APPLY to reduce code at the formulas. 😉
SELECT EmailAddr,
Fullname,
LEFT(Fullname, CHARINDEX('.', Fullname) - 1) FirstName,
PARSENAME(Fullname, 1) LastName,
CASE WHEN Fullname LIKE '%.%.%' THEN PARSENAME(Fullname, 2) END MiddleInitial
FROM Table1
CROSS APPLY (SELECT LEFT(EmailAddr, CHARINDEX('@', EmailAddr) - 1)) Parts(Fullname)
March 12, 2015 at 12:37 pm
That did the trick.
Thanks,
Sqlraider
March 12, 2015 at 6:29 pm
Just for fun, here's a way to do it using the pattern-based string splitter described in my signature links:
SELECT FirstName = MAX(CASE ItemNumber WHEN 1 THEN Item END)
,MiddleInitial = CASE COUNT(*) WHEN 3 THEN MAX(CASE ItemNumber WHEN 3 THEN Item END) END
,LastName = CASE COUNT(*)
WHEN 3 THEN MAX(CASE ItemNumber WHEN 5 THEN Item END)
ELSE MAX(CASE ItemNumber WHEN 3 THEN Item END)
END
FROM
(
SELECT EmailAddr, ItemNumber, Item, [Matched]
FROM
(
SELECT EmailAddr, ItemNumber, Item, [Matched]
,rn=MAX(CASE Item WHEN '@' THEN ItemNumber END) OVER (PARTITION BY EmailAddr)
FROM Table1 a
CROSS APPLY dbo.PatternSplitCM(EmailAddr, '[.@]') b
) a
WHERE ItemNumber < rn AND [Matched] = 0
) a
GROUP BY EmailAddr;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply