Pattern Matching in PL/SQL – LIKE and REGEXP Examples

Pattern Matching in PL/SQL is a technique used to search strings that follow specific patterns. It allows developers to retrieve data not by exact value but by how it matches a form—either partially, loosely, or precisely. Oracle supports two main methods for this:

LIKE operator – traditional, wildcard-based matching
REGEXP_LIKE – advanced matching using regular expressions

Introduction to Pattern Matching in PL/SQL

In PL/SQL, pattern matching refers to evaluating text columns using partial or flexible string criteria. This is useful in scenarios like:

Finding all names starting with “J”
Filtering data that contains specific characters
Matching specific structured values (e.g., IDs, codes, etc.)

Using the LIKE Operator for Pattern Matching in PL/SQL

The LIKE operator allows for pattern matching in the WHERE clause using two wildcards:

%: Matches any number of characters
_: Matches a single character

Syntax:

SELECT column1
FROM table_name
WHERE column1 LIKE 'pattern';

Example: Pattern Matching with LIKE

Let’s create a procedure that searches the AUTHORS table based on a partial last name:

CREATE OR REPLACE PROCEDURE author_sel (
  i_last_name IN AUTHORS.LAST_NAME%TYPE,
  cv_author OUT SYS_REFCURSOR
)
IS
  v_last_name AUTHORS.LAST_NAME%TYPE;
BEGIN
  v_last_name := '%' || UPPER(i_last_name) || '%';
  OPEN cv_author FOR
    SELECT id, first_name, last_name
    FROM authors
    WHERE UPPER(last_name) LIKE v_last_name;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Execution:

VARIABLE x REFCURSOR
EXEC author_sel('rin', :x)
PRINT x

Output:

28  Sumit    Sarin  
54  Cheryl   Riniker

REGEXP_LIKE Function for Pattern Matching in PL/SQL

REGEXP_LIKE is a function introduced in Oracle 10g. It offers powerful pattern matching using regular expressions, which support complex criteria far beyond what LIKE allows.

Syntax:

REGEXP_LIKE(column, pattern [, match_parameter])

column: column to be searched
pattern: regular expression string
match_parameter: optional flags (i for case-insensitive)

Common Regular Expression Metacharacters

Character	Description
`*`	Matches zero or more characters
`.`	Matches any single character
`^`	Matches the start of a string
`$`	Matches the end of a string
`[]`	Defines a set or range of characters
`	`
`()`	Groups expressions
`\`	Escape character

Example: REGEXP_LIKE in a PL/SQL Procedure

CREATE OR REPLACE PROCEDURE author_sel_regexp_like (
  cv_author OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN cv_author FOR
    SELECT id, first_name, last_name
    FROM authors
    WHERE REGEXP_LIKE(last_name, '^har(d|t)man$', 'i');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

This matches:

Hardman
Hartman
Ignores case

Execution:

VARIABLE x REFCURSOR
EXEC author_sel_regexp_like(:x)
PRINT x

Output:

44  Ron     Hardman  
55  Robert  Hartman

Best Practices

Use LIKE for simple pattern matching on short strings.
Use REGEXP_LIKE for complex or structured pattern criteria.
Always normalize data (e.g., use UPPER()) for consistent matching.
Escape special characters in user input for REGEXP security.
Use indexed columns carefully with pattern matching—performance may degrade.

➡️ Learn more about Oracle LIKE Operator Guide

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.