Site icon Radu Pârvu

Top REGEXP_LIKE Patterns for Text Processing in SQL

Radu PARVU

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

ParameterDescription
source_stringColumn or text to search
patternRegular expression
match_parameterOptional flags

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

SymbolMeaning
^Start of string
ALetter A

Matches:

Does NOT match:

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._%+-]+
@ symbol
@
Domain
[A-Za-z0-9.-]+
Dot + extension
\.[A-Za-z]{2,}$

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:

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:

Does NOT match:

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:

Query:

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

Match Parameters

ParameterMeaning
iCase-insensitive
cCase-sensitive
nDot matches newline
mMulti-line mode

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:

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:

Invalid:

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:

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

Matches:

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

FeatureLIKEREGEXP_LIKE
Simple wildcardYesYes
Complex pattern matchingNoYes
Character rangesNoYes
Validation rulesLimitedExcellent
PerformanceFasterSlower

Performance Considerations

REGEXP_LIKE can be expensive

Avoid using it:

Prefer:

WHERE email LIKE '%gmail.com'

Instead of:

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

when simple matching is enough.

Most Important Regexp Symbols

PatternMeaningExample
^Start of string^A
$End of stringZ$
.Any single charactera.c
*Zero or moreab*
+One or moreab+
?Optionalcolou?r
[abc]Any listed character[AEIOU]
[^abc]Not listed characters[^0-9]
[0-9]Digits[0-9]+
[A-Z]Uppercase letters[A-Z]{3}
{n}Exactly n times[0-9]{10}
{n,m}Between n and m[A-Z]{2,5}
\dDigit\d+
\wWord character\w+

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

ETL Validation

Security

Business Rules

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:

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!

Exit mobile version