|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
The INSTR function searches a string for a substring using characters and returns the position in the string that is the first character of a specified occurrence of the substring. INSTR calculates strings using characters as defined by the input character set.
To search a string for a substring using bytes, use INSTR.
A nonzero INTEGER when the search is successful or
0 (zero) when it is not.
INSTR (string , substring [, position [, occurrence]])
The text expression to search.
The string to search for.
A nonzero INTEGER indicating the character of string where the function begins the search. When position is negative, then INSTR counts and searches backward from the end of string. The default value of position is
1, which means that the function begins searching at the first character of string.
An INTEGER indicating which occurrence of string the function should search for. The value of occurrence must be positive. The default values of occurrence is
1, meaning the function searches for the first occurrence of substring.
Example 15-17 Searching Forward for a String
The following example searches the string "Corporate Floor", beginning with the third character, for the string "or". It returns the position in "Corporate Floor" at which the second occurrence of "or" begins.
SHOW INSTR('Corporate Floor','or', 3, 2) 14
Example 15-18 Searching Backward for a String
In this next example, the function counts backward from the last character to the third character from the end, which is the first "o" in "Floor". The function then searches backward for the second occurrence of "or", and finds that this second occurrence begins with the second character in the search string.
SHOW INSTR('Corporate Floor','or', -3, 2) 2