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

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

0
Sandra Smith
Asked:
Sandra Smith
  • 5
  • 4
  • 3
1 Solution
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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