|Oracle® Database SQL Reference
10g Release 1 (10.1)
Part Number B10759-01
INSTR functions search
substring. The function returns an integer indicating the position of the character in
string that is the first character of this occurrence.
INSTR calculates strings using characters as defined by the input character set.
INSTRB uses bytes instead of characters.
INSTRC uses Unicode complete characters.
INSTR2 uses UCS2 code points.
INSTR4 uses UCS4 code points.
position is an nonzero integer indicating the character of
string where Oracle Database begins the search. If
position is negative, then Oracle counts and searches backward from the end of
occurrence is an integer indicating which occurrence of
string Oracle should search for. The value of
occurrence must be positive.
substring can be any of the datatypes
NCLOB. The value returned is of
occurrence must be of datatype
NUMBER, or any datatype that can be implicitly converted to
NUMBER, and must resolve to an integer. The default values of both
occurrence are 1, meaning Oracle begins searching at the first character of
string for the first occurrence of
substring. The return value is relative to the beginning of
string, regardless of the value of
position, and is expressed in characters. If the search is unsuccessful (if
substring does not appear
occurrence times after the
position character of
string), then the return value is 0.
See Also:Table 2-11, "Implicit Type Conversion Matrix" for more information on implicit conversion
The following example searches the string
FLOOR, beginning with the third character, for the string "
OR". It returns the position in
FLOOR at which the second occurrence of "
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL; Instring ---------- 14
In the next example, Oracle counts backward from the last character to the third character from the end, which is the first
FLOOR. Oracle then searches backward for the second occurrence of
OR, and finds that this second occurrence begins with the second character in the search string :
SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring" FROM DUAL; Reversed Instring ----------------- 2
The next example assumes a double-byte database character set.
SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL; Instring in bytes ----------------- 27