instr and substr : INSTR « Char Functions « Oracle PL / SQL

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.

Related examples in the same category

1.	INSTR: returns the first-occurrence position of a character within a string
2.	Simple demo for INSTR function: returns a location within the string where search pattern begins
3.	INSTR: Look for the second occurrence of 'is'
4.	If search pattern is not in the string, the INSTR function returns 0
5.	Combine INSTR and SUBSTR together
6.	If the INSTR pattern is not found, then the entire string would be returned
7.	Get the sub string position by using instr
8.	use instr in an if statement in PL SQL
9.	Ise INSTR to format a column
10.	split column value with ','
11.	Use substr and instr to extract column value