troubleshooting Question

How To Get Extension in an Email Address

Avatar of davidcahan
davidcahanFlag for United States of America asked on
Microsoft SQL Server 2005
6 Comments2 Solutions215 ViewsLast Modified:
I'm using the following function.  I need a part that grabs the extension (the part after the dot) so that i can compare that to all extensions that actually exist.

but of course it needs to be good enough not to get fooled by dcahan@ya.hoo.com .  In that example only "com" should come back.

can someone help me get the extension into a local variable using the code below as a starting point.
alter function dbo.IsValidEmail2(@email as varchar(500))
returns tinyint
as 
begin
 declare @res tinyint
 set @res = 0
 if (
      CHARINDEX(' ',LTRIM(RTRIM(@email))) = 0 
AND        LEFT(LTRIM(@email),1) <> '@' 
AND        RIGHT(RTRIM(@email),1) <> '.' 
AND        CHARINDEX('.',@email,CHARINDEX('@',@email)) - CHARINDEX('@',@email) > 1 
AND        LEN(LTRIM(RTRIM(@email))) - LEN(REPLACE(LTRIM(RTRIM(@email)),'@','')) = 1 
AND        CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email)))) >= 3 
AND        (CHARINDEX('.@',@email) = 0 AND CHARINDEX('..',@email) = 0) 
and       @Email not like '%!%'
and       @Email not like '%#%'
and       @Email not like '%$%'
and       @Email not like '%^%'
and       @Email not like '%&%'
and       @Email not like '%*%'
and       @Email not like '%(%'
and       @Email not like '%)%'
and       @Email not like '%=%'
and       @Email not like '%+%'
and       @Email not like '%{%'	
and       @Email not like '%}%'	
and       @Email not like '%[%'	
and       @Email not like '%]%'	
and       @Email not like '%\%'	
and       @Email not like '%;%'	
and       @Email not like '%:%'	
and       @Email not like '%"%'	
and       @Email not like '%<%'	
and       @Email not like '%>%'	
and       @Email not like '%?%'	
and       @Email not like '%/%'	
)  set @res = 1 
  return @res  
end
ASKER CERTIFIED SOLUTION
EugeneZ
SQL SERVER EXPERT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros