Email Address Validation

• krussell-863890

  SSC Enthusiast

  Points: 134

  More actions

  August 28, 2008 at 7:58 am

  #127248

  Writing an email validation code using select, where, or and like statements. Something like this:

  select emailaddress from contact where ((emailaddress = ''

  -This looks for entries where email was left blank

  I am having trouble making the code to make sure "_" does not appear after "@". If anyone can help me out, thanks.

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  August 28, 2008 at 8:24 am

  #864434

  I'm not doing your job but here is a hint...

  a = lenght of the email column.

  b = position of the '@" in the email column.

  If (a !< b + 4) you may have a valid email address

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• Ken McKelvey

  SSCoach

  Points: 18843

  More actions

  August 28, 2008 at 9:03 am

  #864486

  I use the following code to do some rough validation when importing email addresses.

  SELECT EMail

  FROM

  (

  &nbsp&nbsp&nbsp&nbspSELECT

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspEMail

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('.', REVERSE(EMail)) AS DotPos

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('@', REVERSE(EMail)) AS AtPos

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('@', EMail) AS AtPosStart

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,REVERSE(LEFT(REVERSE(EMail), CHARINDEX('@', REVERSE(EMail)) - 1)) AS DomainName

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,REVERSE(SUBSTRING(REVERSE(EMail), CHARINDEX('@', REVERSE(EMail)) + 1, 255)) AS LocalName

  &nbsp&nbsp&nbsp&nbspFROM YourTable

  ) D

  WHERE NOT

  (

  &nbsp&nbsp&nbsp&nbspD.DotPos > 1

  &nbsp&nbsp&nbsp&nbspAND D.AtPosStart > 1

  &nbsp&nbsp&nbsp&nbspAND D.AtPos > DotPos

  )

  &nbsp&nbsp&nbsp&nbspOR D.LocalName LIKE '%[^[''_=A-Z0-9-.!#$%*/?¦^{}`~&+-]%'

  &nbsp&nbsp&nbsp&nbspOR D.DomainName LIKE '%[^[A-Z0-9-.]%'

  &nbsp&nbsp&nbsp&nbspOR D.EMail LIKE '%..%'

  &nbsp&nbsp&nbsp&nbspOR D.EMail LIKE '%@.%'

  &nbsp&nbsp&nbsp&nbspOR D.EMail LIKE '%.@%'

  &nbsp&nbsp&nbsp&nbspOR D.EMail LIKE '%:@%'

  &nbsp&nbsp&nbsp&nbspOR D.EMail LIKE '.%'

  &nbsp&nbsp&nbsp&nbspOR D.EMail LIKE '@%'

  &nbsp&nbsp&nbsp&nbspOR D.EMail LIKE '%''@%'

  &nbsp&nbsp&nbsp&nbspOR NOT EXISTS

  &nbsp&nbsp&nbsp&nbsp(

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp-- This is a list of valid top and second level domains

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp-- About to become useless

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT *

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM TandSDomains TSD

  &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHERE D.EMail LIKE '%' + TSD.Domain

  &nbsp&nbsp&nbsp&nbsp)