Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
Functions, 53 of 166
instr::=
instr
The "instring" functions search string for 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.
Both string and substring can be any of the datatypes CHAR
, VARCHAR2
, NCHAR
, NVARCHAR2
, CLOB
, or NCLOB
. The value returned is of NUMBER
datatype.
The default values of both position and 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), the return value is 0.
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:
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL; Instring ---------- 14
The next example searches beginning with the third character from the end:
SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring" FROM DUAL; Reversed Instring ----------------- 2
This example assumes a double-byte database character set.
SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL; Instring in bytes ----------------- 27
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|