Oracle SQL - Using the INSTR and SUBSTR Functions

Description

Using the INSTR and SUBSTR Functions

Demo

SQL>
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--  w w w . j a va  2 s  . c om
---------
ACCOUNTING | OUNTING                      | OUN          |  00008.00 |  00008.00
 00000.00

TRAINING   | INING                        | INI          |  00004.00 |  00006.00
 00006.00

SALES      | ES                           | ES           |  00000.00 |  00000.00
 00000.00


DNAME      | SUBSTR1                      | SUBSTR2      |    INSTR1 |    INSTR2
---------- | ---------------------------- | ------------ | --------- | ---------
   INSTR3
---------
HR         |  [N/A]                       |  [N/A]       |  00000.00 |  00000.00
 00000.00


SQL>

The SUBSTR function returns a fragment of the string cut from position n (we start counting from 1).

The length of this fragment is defined by parameter m.

If m is unspecified, all characters will be returned from position n to the end of string.

The INSTR function returns the starting position of the expression in the string.

In a case where n parameter is given, we will start checking only from nth position, but we will still count from the beginning of the string.

If the m parameter is not given, the first iteration of expression occurrence in the string will be returned, and the mth occurrence is returned if m is specified.

Related Topic