Solved

Function to test for valid email format

Posted on 2003-11-14
14
533 Views
Last Modified: 2012-08-13
Strange i couldn't find a similar question already answered...seems a common task.

I want to test a field's value to see if it's a valid email format.

thankx
--steve...
0
Comment
Question by:juststeve
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 9750533
Hey juststeve!

  This depends on what you consider a valid email address.  Generally I would think this includes a "@" and a "." in it.  Would these be your only requirements?

  Public Function ValidEmailAddr(sEmailAddr as String) as Boolean
    if _
      instr(sEmailAddr,"@")>1 and _
      instr(sEmailAddr,".")>3 _
    then
      ValidEmailAddr=True
    endif
  end function

I guess you could also test the last three characters for com/net/org/edu etc.

regards
Jack
0
 

Author Comment

by:juststeve
ID: 9750604
thanks jadedata...how would that code look in TSQL?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9750896
you'll need to get a second opinion on this one,  I thought I was in a VBA flavor forum... sorry.
(but the logic is good, even if the code isn't...)
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9750919
PatIndex('%@%.___%', emailaddress) > 0
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9750952
Actually assuming the service level (COM, NET, EDU, ORG, etc) is 3 characters (not always the case) and the @ cannot be the first or last character before the "." than this would be more accurate:

Patindex('_%@%_.___%', 'emailaddress')

Anthony
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9750963
thanx for bailin' me out acperkins!
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 9750966
That last % should not be there, nor should the quotes arround the column:

Patindex('_%@%_.___', emailaddress)

Anthony
0
 
LVL 6

Assisted Solution

by:acampoma
acampoma earned 150 total points
ID: 9750981
Create Function CheckEmail (@EmailAddress varchar (100)) Returns Bit
as
Begin
Declare @IsEmail Bit
if Charindex('@',@EmailAddress)>1 and Charindex('.',@EmailAddress)>3  Begin
      set @IsEmail= 1
      End
else Begin
      set @IsEmail= 0
      End
Return @IsEmail
End      
   
0
 
LVL 6

Expert Comment

by:acampoma
ID: 9751003
the above example :
select Patindex('_%@%_.___', 'acampoma@mb.sympatico.ca')
will fail with my email address
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9751063
acampoma,

Not sure if you read my comments:
"Actually assuming the service level (COM, NET, EDU, ORG, etc) is 3 characters (not always the case)"

If you are dealing with 2 or 3 characters than it is as simple as changing to:

select Patindex('_%@%_.__%', emailaddress)

Anthony
0
 
LVL 6

Expert Comment

by:acampoma
ID: 9751112
you are Correct acperkins . And your solution is much simpler than mine.
Cheers!
I was not very familiar with the patindex function. I see that it will come to use in the future
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9751207
But actually you bring up a good point and my code was not checking correctly for subdomains (and therefore multiple "."), here is a better solution:

Patindex('_%@%_', emailaddress) > 0 And (Patindex('__.%', Reverse(emailaddress)) > 0 Or Patindex('___.%', Reverse(emailaddress)))

If you want to test it, try is as follows:

Declare @EmailAddress sysname; Set @Email = 'somebody@subdomain.domain.com'
Select Patindex('_%@%_', @Email) * (Patindex('__.%', Reverse(@EmailAddress)) + Patindex('___.%', Reverse(@Email)))

The truth of the matter, is that pattern matching in SQL Server is quite primitive and you are better offr doing this in the front-end or wait for Yukon and Regular Expressions <g>

Anthony
0
 
LVL 19

Assisted Solution

by:Dexstar
Dexstar earned 150 total points
ID: 9757451
juststeve:

> I want to test a field's value to see if it's a valid email format.

As an extra measure, I would add a constraint on the e-mail field in the table.  Create a rule with the following definition:
      ((@value IS NULL) OR (@value  LIKE '%@%.%'))

Hope That Helps,
Dex*
0
 

Author Comment

by:juststeve
ID: 9758431
thankx to all
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question