Oracle PL/SQL Tutorial/PL SQL Operators/Like
LIKE
- LIKE is PL/SQL"s pattern-matching operator.
- LIKE is used to compare a character string against a pattern.
- LIKE is useful for performing wildcard searches.
- LIKE can only be used with character strings.
- LIKE checks to see if the contents of string_variable match the pattern definition.
- If the string matches the pattern, a result of true is returned;
- 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>