SQL INSTR() Function

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

SQL INSTR() function supported Oracle SQL version

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

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

• « SQL Functions