Function to test for valid email format

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

thanks jadedata...how would that code look in TSQL?

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...)

PatIndex('%@%.___%', emailaddress) > 0

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

thanx for bailin' me out acperkins!

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

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

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

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

thankx to all