INSTR, INSTRB, INSTR4

Determines the first position, if any, at which one string occurs within another. If the substring does not occur in the string, 0 is returned. The position returned is always relative to the beginning of SourceExpr. INSTR returns type NUMBER.

SQL syntax

{INSTR | INSTRB | INSTR4} ( SourceExpr, SearchExpr [,m[,n]])

Parameters

INSTR has the parameters:

Parameter	Description
`SourceExpr`	The string to be searched to find the position of `SearchExpr.` Value can be any supported character data types including `CHAR`, `VARCHAR2`, `NCHAR`, `NVARCHAR2`, `CLOB`, or `NCLOB` data types. Both TimesTen and Oracle Database data types are supported.
`SearchExpr`	The substring to be found in string `SourceExpr.` If `SearchExpr` does not occur in `SourceExpr`, zero is returned. If either string is of length zero, `NULL` is returned.
`m`	The optional position at which to begin the search. If `m` is specified as zero, the result is zero. If `m` is positive, the search begins at the `CharExpr2`+`m`. If `m` is negative, the search begins m characters from the end of `CharExpr2`.
`n`	If `n` is specified it must be a positive value and the search returns the position of the `n`th occurrence of `CharExpr1`.

Description

INSTR calculates strings using characters as defined by character set. INSTRB uses bytes instead of characters. INSTR4 uses UCS4 code points.

Examples

The following example uses INSTR to determine the position at which the substring 'ing' occurs in the string 'Washington':

Command> SELECT INSTR ('Washington', 'ing') FROM dual;
< 5 >
1 row found.

The following example uses INSTR to provide the number of employees with a '650' area code as input to the COUNT function:

Command> SELECT COUNT(INSTR(phone_number, '650')) FROM employees;
< 107 >
1 row found.