Using the INSTR and SUBSTR Functions
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-- from ww w . j a va 2 s .c o m --------- 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.