Validating email format with regular expressions in PL SQL

Now, how is this part explained?

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

[A-Za-z0-9._%Ññ+-] This is the first part of an email address, which allows letters from "A" to "Z", from "a" to "z", numbers from 0 to 9, the period, and other characters.

Then comes the @

After the @ comes the domain, then the period, and then the domain extension with a size between 2 and 6 characters.

I hope you find this function useful. You can adapt it with additional validations or enhance it to suit your needs. You can also change it to return a BOOLEAN type instead of VARCHAR2. I'm fine with VARCHAR2 because it makes it easier to use in WHERE conditions of a SELECT statement in SQL Plus.

Below I explain more about "Regular Expressions in Oracle PL SQL".

In Oracle PL/SQL, regular expressions are a powerful tool for searching, comparing, and manipulating text. Oracle includes specific functions for working with regular expressions, such as REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE. Here's how to use them:

1. REGEXP_LIKE

It's used to check whether a text matches a given pattern. It's similar to the LIKE operator, but with support for regular expressions.

SELECT *

FROM EMPLOYEES

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

Example: The query selects employees whose email addresses have a valid format.

2. REGEXP_INSTR

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

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

Example: This returns the position of the first number in the text "abc123def" (in this case, 4).

3. REGEXP_SUBSTR

Gets the substring that matches the specified pattern.

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

Example: This returns 123, as it matches the pattern of numbers in the text.

4. REGEXP_REPLACE

Replaces parts of a text that match a regular expression.

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

Example: This replaces all numbers with #, returning "abc#def".

Common patterns in regular expressions

• ^: Start of string.

• $: End of string.

• [a-z]: Any lowercase letter.

• [A-Z]: Any uppercase letter.

• \d: Any digit.

• +: One or more elements.

• *: Zero or more elements.

• {n,m}: At least n elements, but no more than m.

Best Practices

• Use clear patterns: Keep your expressions as simple as possible for readability.

• Validate input: Regular expressions are useful for validating data such as email addresses, phone numbers, etc.

• Test patterns: Before using them in code, test your regular expressions to avoid unexpected errors.