PL/SQL LIKE Operator

The LIKE operator in PL/SQL is used with the WHERE clause to search for text patterns in columns, such as names, emails or addresses. The % wildcard matches zero or more characters, while _ matches exactly one character.

Syntax:

DECLARE
    emp_cursor SYS_REFCURSOR;
BEGIN
    OPEN emp_cursor FOR
        SELECT column_name
        FROM table_name
        WHERE column_name LIKE pattern;

    DBMS_SQL.RETURN_RESULT(emp_cursor);
END;
/

• emp_cursor: Stores the query results in a cursor.
• column_name: Specifies the column to search.
• pattern: Defines the text pattern to match.
• DBMS_SQL.RETURN_RESULT(): Displays the query results in a result grid.

Example: First, create an employees table with columns emp_id, emp_name, salary.

Example 1: Using LIKE with %

This example retrieves employees whose names start with the letter A.

Query:

DECLARE
    emp_cursor SYS_REFCURSOR;
BEGIN
    OPEN emp_cursor FOR
        SELECT emp_id, emp_name, salary
        FROM employees
        WHERE emp_name LIKE 'A%';

    DBMS_SQL.RETURN_RESULT(emp_cursor);
END;
/

Output:

Example 2: Using LIKE to Match Text Anywhere

This example uses % before and after a to find employees whose names contain son anywhere in the name.

Query:

DECLARE
    emp_cursor SYS_REFCURSOR;
BEGIN
    OPEN emp_cursor FOR
        SELECT emp_id, emp_name, salary
        FROM employees
        WHERE LOWER(emp_name) LIKE '%a%';

    DBMS_SQL.RETURN_RESULT(emp_cursor);
END;
/

Output:

Example 3: Using LIKE with _

This example uses the LIKE operator with four underscores (_) to find employees whose names contain exactly four characters. Each underscore matches one character, so only names with four characters are returned.

Query:

DECLARE
    emp_cursor SYS_REFCURSOR;
BEGIN
    OPEN emp_cursor FOR
        SELECT emp_id, emp_name, salary
        FROM employees
        WHERE emp_name LIKE '____';

    DBMS_SQL.RETURN_RESULT(emp_cursor);
END;
/

Output: