Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function

SQL INSTR Function


SQL INSTR function return sub string position from the original string.


Supported Oracle SQL Version :

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Syntax :

INSTR( original_string, sub_string [, position [, occurrence_time ] ] ) 

INSTR  - Length in character (supported data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB)
INSTRB - Length in bytes (supported only LOB data type)
INSTRC - Length in Unicode (supported CLOB, NCLOB data type)
INSTR2 - Length in code points
INSTR4 - Length in code points

Parameters :

  • original_string is a string.
  • sub_string is find from original string.
  • position is a integer values specified the position to start search. default position is 1 mean begin of the original string.
  • occurrence_time is specifies positive integer number to return that occurrence position. If occurrence value specify 1 then searching at the first character of string.

Example :

Consider following example return the sub string position from original string.

SQL> SELECT INSTR('opal kole', 'l', 6, 1) "INSTR" FROM DUAL;

     INSTR
----------
         8

SQL> SELECT INSTR('lem opalem kole', 'l', 5, 1) "INSTR" FROM DUAL;

     INSTR
----------
         8

SQL> SELECT INSTR('lem opalem kole', 'l', 1, 1) "INSTR" FROM DUAL;

     INSTR
----------
         1