Oracle PL/SQL/Char Functions/INSTR — различия между версиями

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

Версия 13:45, 26 мая 2010

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>