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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 _
  end function

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

juststeveAuthor Commented:
thanks would that code look in TSQL?
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...)
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Anthony PerkinsCommented:
PatIndex('%@%.___%', emailaddress) > 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')

jadedataMS Access Systems CreatorCommented:
thanx for bailin' me out acperkins!
Anthony PerkinsCommented:
That last % should not be there, nor should the quotes arround the column:

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Create Function CheckEmail (@EmailAddress varchar (100)) Returns Bit
Declare @IsEmail Bit
if Charindex('@',@EmailAddress)>1 and Charindex('.',@EmailAddress)>3  Begin
      set @IsEmail= 1
else Begin
      set @IsEmail= 0
Return @IsEmail
the above example :
select Patindex('_%@%_.___', '')
will fail with my email address
Anthony PerkinsCommented:

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)

you are Correct acperkins . And your solution is much simpler than mine.
I was not very familiar with the patindex function. I see that it will come to use in the future
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 = ''
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>


> 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,
juststeveAuthor Commented:
thankx to all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.