regex to check valid email in oracle

Question

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@domain.com';
    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.

p@domain.com

It is not that simple, see https://stackoverflow.com/questions/201323/using-a-regular-expression-to-validate-an-email-address — Wernfried Domscheit, Oct 23 '17 at 14:10

klutt · Answer 1 · 2017-12-14T21:31:02.477

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 .@domain.com 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

regex to check valid email in oracle

1 Answers1