Oracle PL/SQL/Char Functions/INSTR
Содержание
- 1 Combine INSTR and SUBSTR together
- 2 Get the sub string position by using instr
- 3 If search pattern is not in the string, the INSTR function returns 0
- 4 If the INSTR pattern is not found, then the entire string would be returned
- 5 instr and substr
- 6 INSTR: Look for the second occurrence of "is"
- 7 INSTR: returns the first-occurrence position of a character within a string
- 8 Ise INSTR to format a column
- 9 Simple demo for INSTR function: returns a location within the string where search pattern begins
- 10 split column value with ","
- 11 use instr in an if statement in PL SQL
- 12 Use substr and instr to extract column value
Combine INSTR and SUBSTR together
SQL>
SQL>
SQL> SELECT SUBSTR("aaa, bb ccc", INSTR("aaa, bb ccc",", ")) FROM dual;
SUBSTR("
--------
, bb ccc
Get the sub string position by using instr
SQL>
SQL> select instr( "Samantha", "man" ) position from dual;
POSITION
----------
3
1 row selected.
SQL>
SQL> --
If search pattern is not in the string, the INSTR function returns 0
SQL>
SQL>
SQL> -- If search pattern is not in the string, the INSTR function returns 0:
SQL>
SQL> SELECT INSTR("This is a test","abc",1,2) FROM dual;
INSTR("THISISATEST","ABC",1,2)
------------------------------
0
If the INSTR pattern is not found, then the entire string would be returned
SQL>
SQL> -- If the INSTR pattern is not found, then the entire string would be returned
SQL>
SQL> SELECT SUBSTR("aaa bbb c", INSTR("aaa bbb c","zonk")) FROM dual;
SUBSTR("A
---------
aaa bbb c
instr and substr
SQL> create table departments
2 ( deptno NUMBER(2) constraint D_PK
3 primary key
4 , dname VARCHAR2(10)
5 , location VARCHAR2(8)
6 , mgr NUMBER(4)
7 ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7);
1 row created.
SQL> insert into departments values (20,"TRAINING", "DALLAS", 4);
1 row created.
SQL> insert into departments values (30,"SALES", "CHICAGO", 6);
1 row created.
SQL> insert into departments values (40,"HR", "BOSTON", 9);
1 row created.
SQL>
SQL> col substr2 format a7
SQL> select dname
2 , substr(dname,4) as substr1
3 , substr(dname,4,3) as substr2
4 , instr(dname,"I") as instr1
5 , instr(dname,"I",5) as instr2
6 , instr(dname,"I",3,2) as instr3
7 from departments;
DNAME SUBSTR1 SUBSTR2 INSTR1 INSTR2 INSTR3
---------- ------- ------- ---------- ---------- ----------
ACCOUNTING OUNTING OUN 8 8 0
TRAINING INING INI 4 6 6
SALES ES ES 0 0 0
HR 0 0 0
SQL>
SQL> drop table departments;
Table dropped.
INSTR: Look for the second occurrence of "is"
SQL>
SQL>
SQL> -- Look for the second occurrence of "is,"
SQL>
SQL> SELECT INSTR("This is a test","is",1,2) FROM dual;
INSTR("THISISATEST","IS",1,2)
-----------------------------
6
SQL>
SQL>
SQL>
INSTR: returns the first-occurrence position of a character within a string
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> --INSTR: returns the first-occurrence position of a character within a string.
SQL> --it returns a numeric value.
SQL>
SQL> --If it does not find the character value, it returns a 0. For example:
SQL>
SQL> SELECT First_name, INSTR(First_name,"a") AS INSTR FROM Employee;
FIRST_NAME INSTR
---------- ----------
Jason 2
Alison 0
James 2
Celia 5
Robert 0
Linda 5
David 2
James 2
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
SQL>
Ise INSTR to format a column
SQL> CREATE TABLE old_item (
2 item_id CHAR(20),
3 item_desc CHAR(25)
4 );
Table created.
SQL>
SQL> INSERT INTO old_item VALUES("LA-101", "Can, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-102", "Can, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-103", "Bottle, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-104", "Bottle, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-101", "Box, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-102", "Box, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-103", "Shipping Carton, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-104", "Shipping Carton, Large");
1 row created.
SQL>
SQL>
SQL> SELECT item_desc,
2 INSTR(item_desc,
3 ",",
4 1
5 )
6 FROM old_item;
ITEM_DESC INSTR(ITEM_DESC,",",1)
------------------------- ----------------------
Can, Small 4
Can, Large 4
Bottle, Small 7
Bottle, Large 7
Box, Small 4
Box, Large 4
Shipping Carton, Small 16
Shipping Carton, Large 16
8 rows selected.
SQL>
SQL>
SQL> drop table OLD_ITEM;
Table dropped.
Simple demo for INSTR function: returns a location within the string where search pattern begins
SQL> --INSTR: returns a location within the string where search pattern begins.
SQL>
SQL>
SQL> SELECT INSTR("This is a test","is") FROM dual;
INSTR("THISISATEST","IS")
-------------------------
3
SQL>
SQL>
split column value with ","
SQL>
SQL> CREATE TABLE old_item (
2 item_id CHAR(20),
3 item_desc CHAR(25)
4 );
Table created.
SQL>
SQL> INSERT INTO old_item VALUES("LA-101", "Can, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-102", "Can, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-103", "Bottle, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-104", "Bottle, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-101", "Box, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-102", "Box, Large");
1 row created.
SQL>
SQL> SELECT item_desc,
2 SUBSTR(item_desc,
3 1,
4 INSTR(item_desc,
5 ",",
6 1
7 ) -1
8 )
9 FROM old_item;
ITEM_DESC SUBSTR(ITEM_DESC,1,INSTR(
------------------------- -------------------------
Can, Small Can
Can, Large Can
Bottle, Small Bottle
Bottle, Large Bottle
Box, Small Box
Box, Large Box
6 rows selected.
SQL>
SQL> drop table OLD_ITEM;
Table dropped.
SQL>
use instr in an if statement in PL SQL
SQL>
SQL> declare
2 debug_procedure_name long := "A";
3 list_of_debuggable_procs long := "AA";
4 begin
5 if instr( list_of_debuggable_procs,debug_procedure_name ) <> 0 then
6 dbms_output.put_line( "found it" );
7 else
8 dbms_output.put_line( "did not find it" );
9 end if;
10 if instr( "," || list_of_debuggable_procs || ",",
11 "," || debug_procedure_name || "," ) <> 0 then
12 dbms_output.put_line( "found it" );
13 else
14 dbms_output.put_line( "did not find it" );
15 end if;
16 end;
17 /
found it
did not find it
PL/SQL procedure successfully completed.
SQL>
SQL> --
Use substr and instr to extract column value
SQL>
SQL> CREATE TABLE old_item (
2 item_id CHAR(20),
3 item_desc CHAR(25)
4 );
Table created.
SQL>
SQL> INSERT INTO old_item VALUES("LA-101", "Can, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-102", "Can, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-103", "Bottle, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("LA-104", "Bottle, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-101", "Box, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-102", "Box, Large");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-103", "Shipping Carton, Small");
1 row created.
SQL> INSERT INTO old_item VALUES("NY-104", "Shipping Carton, Large");
1 row created.
SQL>
SQL> SELECT item_desc,
2 SUBSTR(item_desc,
3 1,
4 INSTR(item_desc,
5 ",",
6 1
7 ) -1
8 ) CATEGORY,
9 SUBSTR(item_desc,
10 INSTR(item_desc,
11 ",",
12 1
13 ) +2,
14 99
15 ) ITEM_SIZE
16 FROM old_item;
ITEM_DESC CATEGORY ITEM_SIZE
------------------------- ------------------------- -------------------------
Can, Small Can Small
Can, Large Can Large
Bottle, Small Bottle Small
Bottle, Large Bottle Large
Box, Small Box Small
Box, Large Box Large
Shipping Carton, Small Shipping Carton Small
Shipping Carton, Large Shipping Carton Large
8 rows selected.
SQL>
SQL> drop table OLD_ITEM;
Table dropped.
SQL>
SQL>
SQL>