Oracle PL/SQL/Select Query/Like

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

A function using the LIKE operator to return a phone number"s area code

   <source lang="sql">
  

SQL> SQL> -- A function using the LIKE operator to return a phone number"s area code. 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
 6       RETURN SUBSTR(phone_number,1,3);
 7     ELSE
 8      --there is no area code
 9       RETURN "none";
10     END IF;
11  END;
12  /

Function created. SQL> SQL> SQL> select area_code("123456789") from dual; AREA_CODE("123456789")


none SQL> SQL> SQL> select area_code("123-456-7890") from dual;



 </source>
   
  


comments like "%0\%%" escape "\"

   <source lang="sql">
   

SQL> create table history

 2  ( empno      NUMBER(4)
 3  , beginyear  NUMBER(4)
 4  , begindate  DATE
 5  , enddate    DATE
 6  , deptno     NUMBER(2)
 7  , sal       NUMBER(6,2)
 8  , comments   VARCHAR2(60)
 9  , constraint H_PK         primary key
10                            (empno,begindate)
11  , constraint H_BEG_END    check
12                            (begindate < enddate)
13  ) ;

Table created. SQL> SQL> SQL> alter session set NLS_DATE_FORMAT="DD-MM-YYYY"; Session altered. SQL> SQL> insert into history values (1,2000,"01-01-2000","01-02-2000",40, 950,""); 1 row created. SQL> insert into history values (1,2000,"01-02-2000", NULL ,20, 800,"restarted"); 1 row created. SQL> SQL> insert into history values (2,1988,"01-06-1988","01-07-1989",30,1000,""); 1 row created. SQL> insert into history values (2,1989,"01-07-1989","01-12-1993",30,1300,""); 1 row created. SQL> insert into history values (2,1993,"01-12-1993","01-10-1995",30,1500,""); 1 row created. SQL> insert into history values (2,1995,"01-10-1995","01-11-2009",30,1700,""); 1 row created. SQL> insert into history values (2,2009,"01-11-2009", NULL ,30,1600,"just hired"); 1 row created. SQL> SQL> SQL> select empno, begindate, comments

 2  from   history
 3  where  comments like "%0\%%" escape "\";

no rows selected SQL> SQL> drop table history; Table dropped. SQL>



 </source>
   
  


Convert varchar value to upper case and then use like operator

   <source lang="sql">
   

SQL> SQL> CREATE TABLE product (

 2       product_name     VARCHAR2(25),
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE);

Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null); 1 row created. SQL> SQL> SQL> SELECT * FROM product

 2  WHERE  UPPER(product_name) LIKE "%PHOOBAR%";

PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Product Number 50 100 15-JAN-03 SQL> SQL> SQL> SQL> DROP TABLE product; Table dropped.


 </source>
   
  


Create pattern dynamically and use it in like statement

   <source lang="sql">
   

SQL> SQL> SQL> CREATE TABLE emp (

 2    id         NUMBER PRIMARY KEY,
 3    fname VARCHAR2(50),
 4    lname  VARCHAR2(50)
 5  );

Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "E", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. SQL> SQL> SET SERVEROUTPUT ON ESCAPE OFF SQL> SQL> CREATE OR REPLACE PROCEDURE author_sel(i_lname IN emp.lname%TYPE,cv_author IN OUT SYS_REFCURSOR) IS

 2     lnameValue emp.lname%TYPE;
 3  BEGIN
 4
 5     lnameValue := "%"||UPPER(i_lname)||"%";
 6
 7     OPEN cv_author FOR SELECT id, fname, lname FROM emp WHERE UPPER(lname) LIKE lnameValue;
 8
 9  EXCEPTION
10     WHEN OTHERS
11     THEN
12        DBMS_OUTPUT.PUT_LINE(sqlerrm);
13  END;
14  /

Procedure created. SQL> SQL> COL fname FORMAT A20 SQL> COL lname FORMAT A20 SQL> SQL> VARIABLE x REFCURSOR SQL> EXEC author_sel("rin", :x) PL/SQL procedure successfully completed. SQL> SQL> print x no rows selected SQL> SQL> SQL> drop table emp; Table dropped.



 </source>
   
  


Description has "SQL" substring

   <source lang="sql">
   

SQL> create table courses

 2  ( code        VARCHAR2(6)  constraint C_PK
 3                             primary key
 4  , description VARCHAR2(30)
 5  , category    CHAR(3)
 6  , duration    NUMBER(2)
 7  ) ;

Table created. SQL> insert into courses values("SQL","SQL","GEN",4); 1 row created. SQL> insert into courses values("OAU","Java","GEN",1); 1 row created. SQL> insert into courses values("JAV","C++","BLD",4); 1 row created. SQL> insert into courses values("PLS","C","BLD",1); 1 row created. SQL> insert into courses values("XML","XML","BLD",2); 1 row created. SQL> insert into courses values("ERM","ERP","DSG",3); 1 row created. SQL> insert into courses values("PMT","ERP","DSG",1); 1 row created. SQL> insert into courses values("RSD","jQuery","DSG",2); 1 row created. SQL> insert into courses values("PRO","Linux","DSG",5); 1 row created. SQL> insert into courses values("GEN","Oracle","DSG",4); 1 row created. SQL> SQL> SQL> SQL> select *

 2  from    courses
 3  where   description like "%SQL%";

CODE DESCRIPTION CAT DURATION


-------------------------- --- ----------

SQL SQL GEN 4 SQL> SQL> SQL> drop table courses; Table dropped. SQL>



 </source>
   
  


Escape \

   <source lang="sql">
   

SQL> SQL> SQL> create table gift(

 2           gift_id                integer         primary key
 3          ,emp_id                integer
 4          ,register_date              date not null
 5          ,total_price        number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment        varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,message        varchar2(100)
12  );

Table created. SQL> SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values

 2                 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values

 2                 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values

 2                 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");

1 row created. SQL> SQL> select gift_id, message

 2    from gift
 3   where message like "%""%"
 4      or  message like "%\%%" escape "\";

no rows selected SQL> SQL> drop table gift; Table dropped.



 </source>
   
  


Is Like case sensitive

   <source lang="sql">
   

SQL> SQL> CREATE TABLE product (

 2       product_name     VARCHAR2(25),
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE);

Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null); 1 row created. SQL> SQL> SQL> SQL> SELECT INITCAP(product_name),

 2         product_price,
 3         quantity_on_hand,
 4         last_stock_date
 5  FROM   product
 6  WHERE  UPPER(product_name) LIKE "%PHOOBAR%";

INITCAP(PRODUCT_NAME) PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Product Number 50 100 15-JAN-03 SQL> SQL> SQL> DROP TABLE product; Table dropped. SQL> SQL>


 </source>
   
  


Like "%" function with varchar2 type

   <source lang="sql">
  

SQL> CREATE TABLE customers

 2  (
 3     id                NUMBER,
 4     lname         VARCHAR2(40)   CONSTRAINT           customer_lname_nn NOT NULL,
 5     main_phone_number VARCHAR2(25),
 6     credit_limit      NUMBER,
 7     email             VARCHAR2(30)
 8  );

Table created. SQL> select lname, credit_limit

 2  from   customers
 3  where  lname like "Q%";

no rows selected SQL> SQL> drop table customers; Table dropped.


 </source>
   
  


Like with "_" and "%"

   <source lang="sql">
  

SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> -- Like with "_" and "%" SQL> SQL> SELECT First_Name

 2  FROM Employee
 3  WHERE First_Name LIKE"_e%";

FIRST_NAME


Celia SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL> SQL>



 </source>
   
  


Like with "__" (Any two characters)

   <source lang="sql">
  

SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> -- Like with "__" (Any two characters) SQL> SQL> select * from Employee

 2  WHERE First_Name LIKE"A____n";

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL>



 </source>
   
  


Like with % on both sides

   <source lang="sql">
  

SQL> CREATE TABLE customers

 2  (
 3     id                NUMBER,
 4     lname         VARCHAR2(40)   CONSTRAINT           customer_lname_nn NOT NULL,
 5     state_province    VARCHAR2(40),
 6     email             VARCHAR2(30)
 7  );

Table created. SQL> select lname

 2  from   customers
 3  where  lname like "%inl%";

no rows selected SQL> SQL> drop table customers; Table dropped.


 </source>
   
  


Pattern Matching LIKE "%great%"

   <source lang="sql">
   

SQL> SQL> CREATE TABLE SAT (

 2     StudentID  INT NOT NULL,
 3     ExamID     INT NOT NULL,
 4     Mark       INT,
 5     IfPassed   SMALLINT,
 6     Comments   VARCHAR(255),
 7     CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));

Table created. SQL> SQL> SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,"Simple"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1); 1 row created. SQL> SQL> SELECT StudentID, ExamID, Mark, Comments FROM SAT

 2  WHERE Comments LIKE "%great%";

no rows selected SQL> SQL> SQL> drop table SAT; Table dropped.



 </source>
   
  


Second letter is A

   <source lang="sql">
   

SQL> SQL> create table emp

 2  ( empno      NUMBER(4)    constraint E_PK primary key
 3  , ename      VARCHAR2(8)
 4  , init       VARCHAR2(5)
 5  , job        VARCHAR2(8)
 6  , mgr        NUMBER(4)
 7  , bdate      DATE
 8  , sal       NUMBER(6,2)
 9  , comm       NUMBER(6,2)
10  , deptno     NUMBER(2)    default 10
11  ) ;

Table created. SQL> insert into emp values(1,"Tom","N","Coder", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> select empno, init, ename

 2  from    emp
 3  where   ename like "_A%";

no rows selected SQL> SQL> SQL> drop table emp; Table dropped. SQL>



 </source>
   
  


Use % in word ending

   <source lang="sql">
   

SQL> SQL> CREATE TABLE product (

 2       product_name     VARCHAR2(25),
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE);

Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Wodget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 1", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 25, 10000, null); 1 row created. SQL> SQL> SELECT * FROM product WHERE product_name LIKE "Chrome%"; no rows selected SQL> SQL> SQL> DROP TABLE product; Table dropped. SQL> SQL> SQL> SQL>


 </source>
   
  


Use two "%" in Like statement

   <source lang="sql">
  

SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> -- Use two "%" SQL> SQL> SQL> SELECT First_Name

 2  FROM Employee
 3  WHERE First_Name LIKE"%a%";

FIRST_NAME


Jason James Celia Linda David James 6 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL> SQL>



 </source>
   
  


Use two % signs in like

   <source lang="sql">
   

SQL> SQL> CREATE TABLE product (

 2       product_name     VARCHAR2(25),
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE);

Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Wodget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 1", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 25, 10000, null); 1 row created. SQL> SQL> SELECT * FROM product WHERE product_name LIKE "%W_d%"; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC


------------- ---------------- ---------

Small Widget 99 1 15-JAN-03 Wodget 75 1000 15-JAN-02 2 rows selected. SQL> SQL> SQL> DROP TABLE product; Table dropped. SQL>


 </source>
   
  


Use two % signs in one like

   <source lang="sql">
   

SQL> SQL> CREATE TABLE product (

 2       product_name     VARCHAR2(25),
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE);

Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Wodget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 1", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 25, 10000, null); 1 row created. SQL> SQL> SELECT * FROM product WHERE product_name LIKE "%Chrome%"; no rows selected SQL> SQL> SQL> DROP TABLE product; Table dropped. SQL> SQL> SQL> SQL>


 </source>
   
  


Using a NOT operator with like

   <source lang="sql">
  

SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> -- Using a NOT operator with like: SQL> SQL> SELECT ID, First_Name, Last_Name

 2  FROM Employee
 3  WHERE City NOT LIKE"%Van%";

ID FIRST_NAME LAST_NAME


---------- ----------

01 Jason Martin 06 Linda Green 07 David Larry SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL>



 </source>
   
  


Using Pattern Matching LIKE "____ %"

   <source lang="sql">
   

SQL> SQL> CREATE TABLE SAT (

 2     StudentID  INT NOT NULL,
 3     ExamID     INT NOT NULL,
 4     Mark       INT,
 5     IfPassed   SMALLINT,
 6     Comments   VARCHAR(255),
 7     CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));

Table created. SQL> SQL> SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,"Simple"); 1 row created. SQL> SQL> SELECT Name FROM Student WHERE Name LIKE "____ %";

SQL> SQL> SQL> drop table SAT; Table dropped.



 </source>