Friday 11 February 2011

Validating e-mail in PL/SQL

This seems like a frequent request across all languages. I google search can give you all sorts of options for validating a singular e-mail address.

For instance, here's one option using a regular expression:
FUNCTION val_email
  (p_email  IN  VARCHAR2)
  RETURN BOOLEAN IS
BEGIN
  RETURN REGEXP_SUBSTR (p_email, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}') IS NOT NULL OR p_email IS NULL;
END val_email;
Couple this with the common need/want to enter multiple addresses in the one field, I wondered how best to action that requirement - I came up with this:
FUNCTION val_email_string
  (p_email      IN  VARCHAR2
  ,p_separator  IN  VARCHAR2 DEFAULT ',')
  RETURN VARCHAR2 IS
  lt_emails  apex_application_global.vc_arr2;
  lb_valid   BOOLEAN DEFAULT TRUE;
  ln_error   PLS_INTEGER;
BEGIN
  -- split string into separate addresses
  lt_emails := apex_util.string_to_table
                (p_string    => REPLACE(p_email,' ')
                ,p_separator => p_separator);

  << email_val >>
  FOR i IN 1..lt_emails.COUNT LOOP
    lb_valid := val_email(lt_emails(i));
    -- If error occurs, record the problem number and don't bother continuing
    ln_error := i;
    EXIT WHEN NOT lb_valid;
  END LOOP email_val;
  IF NOT lb_valid THEN
    -- return the message with the first problem address
    RETURN 'At least one e-mail not valid: '||lt_emails(ln_error);
  ELSE
    -- no errors occurred
    RETURN NULL;
  END IF;
END val_email_string;
Any suggested improvements?

It was designed for Apex validation with type "Function Returning Error Text"

5 comments:

mnolan said...

Hi Scott

You're probably opening up a can of worms on this one :)

Here's one you can add to the mix which can do it in a single regex, accept different identifiers and up to a limit of X many addresses (example has 100)

^((\s*[a-zA-Z0-9\._%-]+@[a-zA-Z0-9\.-]+\.[a-zA-Z]{2,4}\s*[,;:]){1,100}?)?(\s*[a-zA-Z0-9\._%-]+@[a-zA-Z0-9\.-]+\.[a-zA-Z]{2,4})*$

Here's an explanation which I wrote on it a while back: http://application-express-blog.e-dba.com/?p=158

Cheers
Matt

Scott Wesley said...

Nice - regular expressions were never my strong point... :p

I grew up with Windoze, that's my excuse and I'm sticking to it!

Patrick Wolf said...

Hi Scott,

I think your current code doesn't support multibyte domain names which are allowed nowadays. And I think you can use multibyte characters for the name as well.

Regards
Patrick

Anonymous said...

Scott,

Do you have the APEX Sample page which has this implemented, and can you share the Steps Please...

Scott Wesley said...

Not for this specifically, but check out the APEX builder's guide in relation to PL/SQL validations.