CODEWITHSUNDEEP
regexoracle-database
Nisha Pal
Nisha Pal

Reputation: 21

regex to check valid email in oracle

Below regex is validating almost all valid and invalid email addresses mentioned at :https://blogs.msdn.microsoft.com/testing123/2009/02/06/email-address-test-cases/

Code:

DECLARE 
    v_email webowner.person.email%TYPE;
    v_constant CONSTANT VARCHAR2(300) := '^(([a-zA-Z0-9"_\-])((\.?[a-zA-Z0-9_\/%+="''\-]+)\.?[a-zA-Z0-9+-]*)@(\[((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])\.){3}|((([a-zA-Z0-9\-]+)\.)+))([a-zA-Z]{2,}|(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])\]))$';
    BEGIN
      v_email := '[email protected]';
    if regexp_like(v_email, v_constant) then

      pl('YES: ' || v_email);
    else
      pl('NO: ' || v_email);
    end if;
    END;

But the condition now arises, it is expecting minimum of 2 characters(in username part before "@") whereas I need to have only one mandatory character.

e.g.

[email protected]

Upvotes: 1

Views: 10056

Answers (1)

klutt
klutt

Reputation: 31296

The easiest thing is to just remove the first character, that is ([a-zA-Z0-9"_\-])

But in general, do not validate emails with a regex. If you do, you can be pretty certain that you will do it wrong. The best way to validate an email address is to send a verification mail.

If you, for some reason, really want to use a regex, I'd recommend something like this:

^.*@.+$

This means that the only requirement is that it should have an '@' followed by at least one character. You could also use this to guarantee that it has at least one character before the @:

^.+@.+$

There are standards for addresses, but not all email servers follows these standards. It is fully possible to set up a mail server that handles the address [email protected] or @domain.com, which is not allowed in the standard. But if the address works, why prevent it? Shortly, EVERYTHING to the left of @ are meant to be handled by the server so do not validate that.

Actually, the only reason I can see to validate an email like this, is to make sure that the user did not enter his phone number or name or something like that instead of the email address. For this purpose, it is completely sufficient to just check that the address contains a '@', which you can do with this:

^.*@.*$

For more information on how you can implement a valid regex and why you should not do that, here is some reading:

http://www.regular-expressions.info/email.html

https://davidcel.is/posts/stop-validating-email-addresses-with-regex/

Using a regular expression to validate an email address

Upvotes: 1

Related Questions