Introduction

We continue further with another feature that is unjustly ignored:

REGEXP_LIKE in Oracle Database allows you to search text using regular expressions (regex). It is far more powerful than:
• LIKE
• INSTR
• SUBSTR

Typical use cases

You can use it to:
• Validate emails
• Check phone numbers
• Find invalid data
• Filter names
• Detect patterns
• Clean datasets
• Enforce business rules

Basic Syntax

REGEXP_LIKE(source_string, pattern [, match_parameter])

Parameters

Examples

First Simple Example

Find names starting with A

SELECT emp_name
FROM employees_demo
WHERE REGEXP_LIKE(emp_name, '^A');

Output:

EMP_NAME                                          
--------------------------------------------------
Aglae

Explanation

Matches:

• Alice
• Andrew

Does NOT match:

• Mark
• Diana

Example 1 — Validate Email Addresses

Query

SELECT email
FROM demo_users
WHERE REGEXP_LIKE(
    email,
    '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
);

Explanation

Beginning of email

^[A-Za-z0-9._%+-]+

• • Starts with letters/numbers
• Allows:
  • .
  • _
  • %
  • +
  • –

@ symbol

@

Domain

[A-Za-z0-9.-]+

Dot + extension

\.[A-Za-z]{2,}$

• .com
• .org
• .net

The query selects the valid email addresses:

Example 2 — Validate 10-digit Phone Numbers

Query:

SELECT phone
FROM demo_users
WHERE REGEXP_LIKE(phone, '^[0-9]{10}$');

Explanation

Meaning:
• Start of string
• Exactly 10 digits
• End of string
Valid:

9876543210

Invalid:

• 12345
• 98A7654321

Here is the list of users with valid phone numbers:

Example 3 — Names Containing Only Alphabets

Query:

SELECT name
FROM demo_users
WHERE REGEXP_LIKE(name, '^[A-Za-z]+$');

Matches:

• Alice
• Bob

Does NOT match:

• Alice123
• Bob_1

Example 4 — Find Rows With Numbers in Text

Query:

SELECT name
FROM demo_users
WHERE REGEXP_LIKE(name, '[0-9]');

Finds any name containing at least one digit:

Example 5 — Names Ending With “son”

Query:

SELECT customer_name
FROM customers
WHERE REGEXP_LIKE(customer_name, 'son$');

Matches:

• Jackson
• Anderson

Example 6 — Case-Insensitive Search

Query:

SELECT name
FROM demo_users
WHERE REGEXP_LIKE(name, '^alice$', 'i');

Match Parameters

This works:

This doesn’t (case sensitive):

Example 7 — Validate PAN Card Format (India)

Query:

SELECT pan_number
FROM customers
WHERE REGEXP_LIKE(
    pan_number,
    '^[A-Z]{5}[0-9]{4}[A-Z]$'
);

Format

Example valid PAN:

ABCDE1234F

Breakdown:

• 5 uppercase letters
• 4 digits
• 1 uppercase letter

Example 8 — Validate Date Format: DD-MM-YYYY

Query sample:

SELECT order_date
FROM orders
WHERE REGEXP_LIKE(
    order_date,
    '^[0-9]{2}-[0-9]{2}-[0-9]{4}$'
);

Valid:

• 12-05-2025

Invalid:

• 5-12-2025

Note: In this case, Regexp validates format, not actual calendar correctness.

Example 9 — Multiple Alternatives Using OR

SELECT file_name
FROM files
WHERE REGEXP_LIKE(file_name, '\.(pdf|docx|xlsx)$');

Matches:

• report.pdf
• data.xlsx

Tip: it is easy to test by using the dual table:

SELECT *
FROM dual
WHERE REGEXP_LIKE('\text1.ppt', '\.(pdf|docx|xlsx)$');

Example 10 — Find fields with Repeated Characters

SELECT username
FROM users
WHERE REGEXP_LIKE(username, '(.)\1');

Explanation

(.)\1

• (.) captures any character
• \1 checks if same character repeats

Matches:

• cool
• apple

Sample Table for Practice

CREATE TABLE demo_users (
    id NUMBER,
    name VARCHAR2(100),
    email VARCHAR2(200),
    phone VARCHAR2(20)
);

Table DEMO_USERS created.

Insert Sample Data

INSERT INTO demo_users VALUES (1, 'Alice', 'alice@gmail.com', '9876543210');
INSERT INTO demo_users VALUES (2, 'Bob', 'bob@yahoo.com', '9123456789');
INSERT INTO demo_users VALUES (3, 'Charlie', 'invalid-email', '12345');
INSERT INTO demo_users VALUES (4, 'David', 'david@test.org', '9988776655');
COMMIT;

Using REGEXP_LIKE in PL/SQL

Using REGEXP_LIKE in PL/SQL is possible as with any usual SQL function.

REGEXP_LIKE vs LIKE

Performance Considerations

REGEXP_LIKE can be expensive

Avoid using it:

• On huge datasets unnecessarily
• In indexed search predicates where simple LIKE works

Prefer:

WHERE email LIKE '%gmail.com'

Instead of:

WHERE REGEXP_LIKE(email, 'gmail\.com$')

when simple matching is enough.

Most Important Regexp Symbols

Common Mistakes

Forgetting ^ and $

Wrong: ‘[0-9]{10}’

Correct: ‘^[0-9]{10}$’

This matches:

abc1234567890xyz

Not Escaping Special Characters

Real-World Use Cases

Data Quality Checks

• Invalid emails
• Bad phone numbers
• Wrong IDs

ETL Validation

• Validate incoming CSV data

Security

• Detect suspicious inputs

Business Rules

• Employee code validation
• Customer ID formats

Tips for Learning Regex Faster

1. Start simple
2. Test patterns incrementally
3. Use sample datasets
4. Learn anchors first (^ and $)
5. Practice character classes daily

Final Thoughts

REGEXP_LIKE is one of the most powerful text-processing features in Oracle SQL.

Once mastered, it becomes extremely useful for:

• Automation
• Data validation
• Cleansing
• ETL pipelines
• Reporting
• Compliance checks

Regexp are a lot of fun and I plan to continue the journey with string and text processing capabilities of the database. Until the next one, please drop me a line or leave a comment below. Many Thanks!