Validating email format with regular expressions in PL SQL

How to validate the format of an email address using Oracle PL/SQL.

You've probably encountered this situation: you have a table and a maintenance screen where users register email addresses, which you then use to send information directly from the database. However, you later encounter processing errors simply because the email address format is incorrect.

To solve this, here is a powerful and practical validation function using regular expressions that even allows verifying a list of multiple emails separated by semicolons (;).

Function with Regular Expressions in PL/SQL

CREATE OR REPLACE FUNCTION Fvalida_Email (p_Email VARCHAR2) RETURN VARCHAR2 IS
-- This function returns 'S' if the email is formatted correctly, 'N' if it is not.
-- Allows a list of emails separated by semicolons.
--
CURSOR Cur_Email (p_sub_email VARCHAR2) IS
  SELECT 'S'
  FROM Dual
  WHERE REGEXP_LIKE (p_sub_Email, '^[A-Za-z0-9._%Ññ+-]+@[A-Za-z0-9.-]+\.[A-Za-z0-9]{2,6}$');
--
v_valid VARCHAR2 (1) := 'S';
v_aux_valid VARCHAR2 (1);
v_pos NUMBER;
v_email_remaining VARCHAR2 (2000) := p_email;
v_email VARCHAR2 (2000);
BEGIN
  -- First we go through the row looking for several email addresses separated by semicolons.
  LOOP
    v_pos := INSTR (v_email_remaining, ';');
    IF v_pos > 0 THEN
      v_email := SUBSTR (v_email_remaining, 1, v_pos - 1);
      v_email_remaining := LTRIM (SUBSTR(v_email_remaining, v_pos + 1));
    ELSE
      v_email := v_email_remaining;
      v_email_remaining := NULL;
    END IF;
    
    -- Validating a single email address
    IF NVL (LENGTH (v_email), 0) < 5 THEN
      v_valid := 'N';
    ELSE
      v_aux_valid := NULL;
      OPEN Cur_Email (v_email);
      FETCH Cur_Email INTO v_aux_valid;
      CLOSE Cur_Email;
      v_valid := NVL (v_aux_valid, 'N');
    END IF;
    
    EXIT WHEN v_pos <= 0 OR v_valid = 'N';
  END LOOP;
  
  RETURN (NVL (v_valid, 'N'));
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.Put_line ('Error validating email: ' || p_email || '. ' || SQLERRM);
    RETURN ('N');
END Fvalida_Email;

Breaking Down the Regex Pattern

Let's explain exactly how the core validation string works:

REGEXP_LIKE (p_sub_Email, '^[A-Za-z0-9._%Ññ+-]+@[A-Za-z0-9.-]+\.[A-Za-z0-9]{2,6}$')

• ^[A-Za-z0-9._%Ññ+-]+: This defines the first part of the email address (before the @). It allows alphanumeric characters (A-Z, a-z, 0-9), periods, underscores, percentages, hyphens, plus signs, and specifically includes the character "Ñ/ñ". The + ensures there is at least one character.
• @: Matches the literal "@" symbol required in every email.
• [A-Za-z0-9.-]+: Matches the domain name (e.g., "gmail", "outlook", "mycompany"), allowing letters, numbers, periods, and hyphens.
• \.: Escapes the period character to match a literal dot before the domain extension.
• [A-Za-z0-9]{2,6}$: Matches the domain extension (TLD) at the very end of the string ($). It specifies that the extension must have a minimum length of 2 characters and a maximum of 6 (such as .com, .net, or .online).

You can easily adapt this function with additional validations or modify it to return a BOOLEAN type instead of VARCHAR2. Returning VARCHAR2 ('S'/'N') is highly convenient because it makes it simple to filter records directly inside the WHERE clause of a standard SQL query.

Regular Expressions in Oracle PL/SQL

In Oracle, regular expressions are a built-in feature for parsing, searching, and manipulating text. Beyond REGEXP_LIKE, Oracle provides three other core functions:

1. REGEXP_LIKE

Checks whether a text string matches a specific pattern. It acts like an advanced version of the traditional LIKE operator.

SELECT *
FROM EMPLOYEES
WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

2. REGEXP_INSTR

Returns the starting position of a substring that matches the regular expression pattern inside a text.

SELECT REGEXP_INSTR('abc123def', '\d+') AS posicion FROM dual;

Example output: This returns 4, which is the exact character position where the numeric sequence starts in "abc123def".

3. REGEXP_SUBSTR

Extracts and returns the exact substring that matches the specified pattern.

SELECT REGEXP_SUBSTR('abc123def', '\d+') AS subcadena FROM dual;

Example output: This extracts and returns 123, matching the numeric digits pattern.

4. REGEXP_REPLACE

Searches for a regex pattern and replaces the matching text with a new specified string.

SELECT REGEXP_REPLACE('abc123def', '\d+', '#') AS modified_text FROM dual;

Example output: This replaces the numbers with an octothorpe, returning "abc#def".

Common Regex Patterns Reference

• ^ : Start of the string.
• $ : End of the string.
• [a-z] : Any lowercase letter.
• [A-Z] : Any uppercase letter.
• \d : Any numeric digit.
• + : Matches one or more elements.
• * : Matches zero or more elements.
• {n,m} : Matches between n and m elements.

Best Practices

• Use clear patterns: Keep your expressions as clean as possible to maintain code readability for future modifications.
• Validate input data: Regex is highly recommended for preprocessing user inputs like email addresses, telephone numbers, and zip codes.
• Test thoroughly: Always run test strings against your expressions in a development environment before pushing them to production to prevent unexpected match failures.