|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
Functions, 55 of 177
The "in string" 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 codepoints.
INSTR4 uses UCS4 codepoints.
positionis an nonzero integer indicating the character of
stringwhere Oracle begins the search. If
positionis negative, then Oracle counts and searches backward from the end of
occurrenceis an integer indicating which occurrence of
stringOracle should search for. The value of
occurrencemust be positive.
substring can be any of the datatypes
NCLOB. The value returned is of
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.
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 "
In the next example, Oracle counts backward from the last character to the third character from the end, which is the first "O" in "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
This example assumes a double-byte database character set.