Oracle PL/SQL/Select Query/Like
Содержание
- 1 A function using the LIKE operator to return a phone number"s area code
- 2 comments like "%0\%%" escape "\"
- 3 Convert varchar value to upper case and then use like operator
- 4 Create pattern dynamically and use it in like statement
- 5 Description has "SQL" substring
- 6 Escape \
- 7 Is Like case sensitive
- 8 Like "%" function with varchar2 type
- 9 Like with "_" and "%"
- 10 Like with "__" (Any two characters)
- 11 Like with % on both sides
- 12 Pattern Matching LIKE "%great%"
- 13 Second letter is A
- 14 Use % in word ending
- 15 Use two "%" in Like statement
- 16 Use two % signs in like
- 17 Use two % signs in one like
- 18 Using a NOT operator with like
- 19 Using Pattern Matching LIKE "____ %"
A function using the LIKE operator to return a phone number"s area code
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;
comments like "%0\%%" escape "\"
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>
Convert varchar value to upper case and then use like operator
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.
Create pattern dynamically and use it in like statement
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.
Description has "SQL" substring
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>
Escape \
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.
Is Like case sensitive
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>
Like "%" function with varchar2 type
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.
Like with "_" and "%"
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>
Like with "__" (Any two characters)
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>
Like with % on both sides
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.
Pattern Matching LIKE "%great%"
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.
Second letter is A
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>
Use % in word ending
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>
Use two "%" in Like statement
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>
Use two % signs in like
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>
Use two % signs in one like
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>
Using a NOT operator with like
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>
Using Pattern Matching LIKE "____ %"
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.