INSTR

Description

INSTR

is a row function that returns an integer indicating the position of a character within a string that is the first character of the occurrence of a substring. The

INSTR

function is similar to the FIND function in Excel, except that the first letter is position 0 and the order of the arguments is reversed.

Syntax

INSTR

(

search_string

substring

position

occurrence

)

Return Value

Returns one value per row of type

INTEGER

. The first position is indicated with the value of zero (0).

Input Parameters

search_string: Required. The name of a field or expression of type
TEXT
(or a literal string).
substring: Required. A literal string or name of a field that specifies the substring to search for in search_string. Note that to search for the double quotation mark (
"
) as a literal string, you must escape it with another double quotation mark:
""
position: Optional. An integer that specifies at which character in search_string to start searching for substring. A value of 0 (zero) starts the search at the beginning of search_string. Use a positive integer to start searching from the beginning of search_string, and use a negative integer to start searching from the end of search_string. When no position is specified,
INSTR
searches at the beginning of the string (0).
occurrence: Optional. A positive integer that specifies which occurrence of substring to search for. When no occurrence is specified,
INSTR
searches for the first occurrence of the substring (1).

Examples

Return the position of the first occurrence of the substring "http://" starting at the end of the url
field:

INSTR([url], "http://", -1, 1)

The following expression searches for the second occurrence of the substring "st" starting at the beginning of the string "bestteststring". INSTR finds that the substring starts at the seventh character in the string, so it returns 6:

INSTR("bestteststring", "st", 0, 2)

The following expression searches backward for the second occurrence of the substring "st" starting at 7 characters before the end of the string "bestteststring".

INSTR

finds that the substring starts at the third character in the string, so it returns 2:

INSTR("bestteststring", "st", -7, 2)