CODEWITHSUNDEEP
regexoracle-databaseplsqlerror-handlingregexp-like
Jay Rizzi
Jay Rizzi

Reputation: 4304

Oracle Regex_like function: returning what failed

I have an oracle function that is returning whether an email address is valid or not

CREATE OR REPLACE FUNCTION valid_email (p_email in varchar2)
                                      return string is
  v_return  varchar2(255);
  cemailregexp constant varchar2(1000) := '^[a-z0-9!#$%&''*+/=?^_`{|}~-]+(\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*@([a-z0-9]([a-z0-9-]*[a-z0-9])?\.)+([A-Z]{2}|arpa|biz|com|info|intww|name|net|org|pro|aero|asia|cat|coop|edu|gov|jobs|mil|mobi|museum|pro|tel|travel|post)$';

BEGIN
  if regexp_like(p_email,cemailregexp,'i') then
     v_return := p_email;
  else
     v_return := null;
  end if;
  return v_return;

END;

if the email passes the regex, it returns the email, otherwise the return result is NULL

What I would love to know is what part of the input string failed, why did the regex_like fail?

instead of retuning just null if bad, maybe a new variable for reason

i would like email mgptva;[email protected] to return:

v_return_msg= "invalid character ';' at position 7

I am sorry for not including my attempts toward this, I dont even know where to start, google searching hasnt been fruitful

Upvotes: 0

Views: 115

Answers (1)

user272735
user272735

Reputation: 10648

Regular expressions either match or don't match. That's very much everything you get out of them.

Email addresses are complex and very difficult to validate with a regular expression. If your core business is not to build an email address validator I recommend that don't waste your time here anymore but move on.

I very much agree with this article Stop Validating Email Addresses With Regex and my canonical email verification is:

create or replace function is_valid_email(p_email in varchar2) return number is
begin
  return regexp_count(p_email, '^\S+@\S+\.\S+$');
end;

If you want to see one state of the art implementation check Perl's Email::Valid module.

Upvotes: 1

Related Questions