Oracle PL/SQL Tutorial/PL SQL Operators/Like

LIKE

1. LIKE is PL/SQL"s pattern-matching operator.
2. LIKE is used to compare a character string against a pattern.
3. LIKE is useful for performing wildcard searches.
4. LIKE can only be used with character strings.
5. LIKE checks to see if the contents of string_variable match the pattern definition.
6. If the string matches the pattern, a result of true is returned;
7. otherwise, the expression evaluates to false.

The Syntax for LIKE

string_variable LIKE pattern

Use like in PL/SQL

SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE FUNCTION area_code (phone_number IN VARCHAR2)
  2  RETURN VARCHAR2 AS
  3  BEGIN
  4    IF phone_number LIKE "___-___-____" THEN
  5      RETURN SUBSTR(phone_number,1,3);
  6    ELSE
  7      RETURN "none";
  8    END IF;
  9  END;
 10  /
Function created.
SQL>
SQL>

Use the LIKE operator

SQL>
SQL> set feedback on escape ~
SQL>
SQL> CREATE TABLE authors (
  2    id         NUMBER PRIMARY KEY,
  3    first_name VARCHAR2(50),
  4    last_name  VARCHAR2(50)
  5  );
Table created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (1, "Marlene", "Theriault");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (2, "Rachel", "Carmichael");
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (3, "James", "Viscusi");
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> CREATE OR REPLACE PROCEDURE author_sel (
  2     i_last_name IN AUTHORS.LAST_NAME%TYPE,
  3     cv_author IN OUT SYS_REFCURSOR)
  4  IS
  5     v_last_name AUTHORS.LAST_NAME%TYPE;
  6  BEGIN
  7
  8     v_last_name := "%"||UPPER(i_last_name)||"%";
  9
 10     OPEN cv_author FOR
 11     SELECT id, first_name, last_name
 12     FROM authors
 13     WHERE UPPER(last_name) LIKE v_last_name;
 14
 15  EXCEPTION
 16     WHEN OTHERS
 17     THEN
 18        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 19  END;
 20  /
Procedure created.
SQL>
SQL> COL first_name FORMAT A20
SQL> COL last_name FORMAT A20
SQL>
SQL> VARIABLE x REFCURSOR
SQL> EXEC author_sel("u", :x)
PL/SQL procedure successfully completed.
SQL>
SQL> print x

        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 Marlene              Theriault
         3 James                Viscusi
2 rows selected.
SQL> drop table authors;
Table dropped.

Validate a zip code

SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE FUNCTION area_code (phone_number IN VARCHAR2)
  2  RETURN VARCHAR2 AS
  3  BEGIN
  4    IF phone_number LIKE "___-___-____" THEN
  5      RETURN SUBSTR(phone_number,1,3);
  6    ELSE
  7      RETURN "none";
  8    END IF;
  9  END;
 10  /
Function created.
SQL>
SQL>