Oracle PL/SQL Tutorial/PL SQL Operators/Like

Материал из SQL эксперт
Перейти к: навигация, поиск

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



   <source lang="sql">

string_variable LIKE pattern</source>


Use like in PL/SQL

   <source lang="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></source>


Use the LIKE operator

   <source lang="sql">

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.</source>


Validate a zip code

   <source lang="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></source>