instr and substr : INSTR « Character String Functions « Oracle PL/SQL Tutorial

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.

11.6.INSTR
	11.6.1.	INSTR(x, find_string [, start] [, occurrence]) searchs for find_string in x
	11.6.2.	INSTR ('in-string') is a function used to find patterns in strings
	11.6.3.	Display the position where the second occurrence of e occurs in employee's first name
	11.6.4.	INSTR returns a location within the string where search pattern begins.
	11.6.5.	Look for the second occurrence of 'is'
	11.6.6.	If search pattern is not in the string, the INSTR function returns 0
	11.6.7.	Using SUBSTR and INSTR together
	11.6.8.	If the INSTR pattern is not found, then the entire string would be returned
	11.6.9.	Use dates in INSTR functions
	11.6.10.	instr and substr