• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

Function to test for valid email format

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
juststeve
Asked:
juststeve
  • 5
  • 3
  • 3
  • +2
3 Solutions
 
jadedataMS Access Systems CreatorCommented:
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
 
juststeveAuthor Commented:
thanks jadedata...how would that code look in TSQL?
0
 
jadedataMS Access Systems CreatorCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
PatIndex('%@%.___%', emailaddress) > 0
0
 
Anthony PerkinsCommented:
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
 
jadedataMS Access Systems CreatorCommented:
thanx for bailin' me out acperkins!
0
 
Anthony PerkinsCommented:
That last % should not be there, nor should the quotes arround the column:

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

Anthony
0
 
acampomaCommented:
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
 
acampomaCommented:
the above example :
select Patindex('_%@%_.___', 'acampoma@mb.sympatico.ca')
will fail with my email address
0
 
Anthony PerkinsCommented:
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
 
acampomaCommented:
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
 
Anthony PerkinsCommented:
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
 
DexstarCommented:
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
 
juststeveAuthor Commented:
thankx to all
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now