Oracle PL/SQL/Char Functions/INSTR

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

Combine INSTR and SUBSTR together

   <source lang="sql">

SQL> SQL> SQL> SELECT SUBSTR("aaa, bb ccc", INSTR("aaa, bb ccc",", ")) FROM dual; SUBSTR("


, bb ccc


 </source>
   
  


Get the sub string position by using instr

   <source lang="sql">
 

SQL> SQL> select instr( "Samantha", "man" ) position from dual;

 POSITION

        3

1 row selected. SQL> SQL> --

 </source>
   
  


If search pattern is not in the string, the INSTR function returns 0

   <source lang="sql">

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
          
        
 </source>
   
  


If the INSTR pattern is not found, then the entire string would be returned

   <source lang="sql">

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


 </source>
   
  


instr and substr

   <source lang="sql">
 

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.


 </source>
   
  


INSTR: Look for the second occurrence of "is"

   <source lang="sql">

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>


 </source>
   
  


INSTR: returns the first-occurrence position of a character within a string

   <source lang="sql">

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>


 </source>
   
  


Ise INSTR to format a column

   <source lang="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> 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.

 </source>
   
  


Simple demo for INSTR function: returns a location within the string where search pattern begins

   <source lang="sql">

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>


 </source>
   
  


split column value with ","

   <source lang="sql">
 

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>

 </source>
   
  


use instr in an if statement in PL SQL

   <source lang="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> --

 </source>
   
  


Use substr and instr to extract column value

   <source lang="sql">
 

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>

 </source>