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
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?
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 '*?*'));
silemone
[a-zA-Z][a-zA-Z0-9]*\@[a-z
(com|us|fr|net|org|ca|....
ASKER
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
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?
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?
ASKER
Have that, but this is a fall-back just in case.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
but he wants to do this in his sql cactus...
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
> 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
ASKER
Actually, the function is a good idea and thanks!
BTW, I'm a she
BTW, I'm a she
ASKER
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.
ASKER
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.
But silemone, you also gave me an idea on sometihng else so I do appreciate and thank you for your input.
You are welcome!
/gustav
/gustav