Check for illegal characters in an email address

I have a table of interal email addresses and need to check, when a user enters a new email address, that it does not contain any illegal characters.  The properform is firstnamelastname@thisbank.com.

However, I tried the attached which works but I would have to list out all the characters that are not allowed (except the @ sign).  I was just wondering if there is a better way?
SELECT tblEmailAddress.BR_NBR, tblEmailAddress.EmailAddressTo
FROM tblEmailAddress
WHERE (((tblEmailAddress.EmailAddressTo) Like '*!*' Or (tblEmailAddress.EmailAddressTo) Like '*?*'));

Open in new window

Sandra SmithRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

silemoneCommented:
[a-zA-Z][a-zA-Z0-9]*\@[a-zA-Z][a-zA-Z]*\.(com|us|net|org)
0
silemoneCommented:
(com|us|fr|net|org|ca|.......add all extension...above was example

0
Sandra SmithRetiredAuthor Commented:
I think I understand.  Only I am looking for illegal characters, so I would reverse the seraching?  That is, rewriting my query to the new snippet?  What about quotes?
SELECT tblEmailAddress.BR_NBR, tblEmailAddress.EmailAddressTo
FROM tblEmailAddress
WHERE tblEmailAddress.EmailAddressTo NOT LIKE [a-zA-Z][a-zA-Z0-9]*\@[a-zA-Z][a-zA-Z]*\.com

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

silemoneCommented:
oh...sorry...you can't use regular expressions in sql...
not to tell you how to do this, but shouldn't you be checking before the user has a chance to enter data to table?
0
Sandra SmithRetiredAuthor Commented:
Have that, but this is a fall-back just in case.
0
Gustav BrockCIOCommented:
Here is a function for this.

/gustav
Function IsEmailAddress(ByVal strEmailAddr As String) As Boolean
 
' Checks if strEMailAddr could represent a valid e-mail address.
' Does not check validity of domain name.
 
  ' Allowed characters.
  Const cstrValidChars    As String = "@_-.0123456789abcdefghijklmnopqrstuvwxyz"
  Const cstrDot           As String = "."
  Const cstrAt            As String = "@"
  ' Minimum length of an e-mail address (a@a.ca).
  Const cintAddressLenMin As Integer = 6
  
  Dim strValidChars       As String
  Dim booFailed           As Boolean
  Dim intPos              As Integer
  Dim intI                As Integer
 
  ' Convert to lowercase.
  strEmailAddr = LCase(strEmailAddr)
  ' Check that strEMailAddr contains allowed characters only.
  For intI = 1 To Len(strEmailAddr)
    If InStr(cstrValidChars, Mid(strEmailAddr, intI, 1)) = 0 Then
      booFailed = True
    End If
  Next
  If booFailed = False Then
    ' Check that the first character is not cstrAt.
    booFailed = Left(strEmailAddr, 1) = cstrAt
    If booFailed = False Then
      ' Check that the first character is not a cstrDot.
      booFailed = Left(strEmailAddr, 1) = cstrDot
      If booFailed = False Then
        ' Check that length of strEMailAddr exceeds
        ' minimum length of an e-mail address.
        intPos = Len(strEmailAddr)
        booFailed = (intPos < cintAddressLenMin)
        If booFailed = False Then
          ' Check that none of the last two characters of strEMailAddr is a dot.
          booFailed = (InStr(intPos - 1, strEmailAddr, cstrDot) > 0)
          If booFailed = False Then
            ' Check that strEMailAddr does contain a cstrAt.
            intPos = InStr(strEmailAddr, cstrAt)
            booFailed = (intPos = 0)
            If booFailed = False Then
              ' Check that strEMailAddr does contain one cstrAt only.
              booFailed = (InStr(intPos + 1, strEmailAddr, cstrAt) > 0)
              If booFailed = False Then
                ' Check that the character leading cstrAt is not cstrDot.
                booFailed = (Mid(strEmailAddr, intPos - 1, 1) = cstrDot)
                If booFailed = False Then
                  ' Check that the character following cstrAt is not cstrDot.
                  booFailed = (Mid(strEmailAddr, intPos + 1, 1) = cstrDot)
                  If booFailed = False Then
                    ' Check that strEMailAddr contains at least one cstrDot
                    ' following the sign after cstrAt.
                    booFailed = Not (InStr(intPos, strEmailAddr, cstrDot) > 1)
                  End If
                End If
              End If
            End If
          End If
        End If
      End If
    End If
  End If
 
  IsEmailAddress = Not booFailed
 
End Function

Open in new window

0

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
silemoneCommented:
but he wants to do this in his sql cactus...
0
Gustav BrockCIOCommented:
Yes, but

> this is a fall-back just in case.

and this is the case. It is way too clumsy to do this in SQL. T-SQL or similar code perhaps, but not in a query.

/gustav
0
Sandra SmithRetiredAuthor Commented:
Actually, the function is a good idea and thanks!

BTW, I'm a she
0
Sandra SmithRetiredAuthor Commented:
silemone, thank you for trying to help, but I think catus_data's approach will fill my needs.  I was hoping to do this "my way", but it would be messy.  
0
Sandra SmithRetiredAuthor Commented:
catus_data.  Thank you so much.  I copy/pasted into a module, called it in my query and works perfectly.  Thank you!  Part of this "fall back" was a recent developing of uploading the email addresses and I  need a way to quickly plow through them to be sure all are in the proper format, something I could not control via the input form.  I get a result set with the ones that need to be fixed-which was precsiely what I was looking form.

But silemone, you also gave me an idea on sometihng else so I do appreciate and thank you for your input.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
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 Development

From novice to tech pro — start learning today.