Use the INSTR function to find the position of a substring - MaxCompute

Returns the position of a substring within a string, with optional start position and occurrence number.

Syntax

bigint instr(string <str1>, string <str2>[, bigint <start_position>[, bigint <nth_appearance>]])

Parameter	Required	Type	Description
`str1`	Yes	STRING	The string to search. BIGINT, DOUBLE, DECIMAL, and DATETIME values are implicitly converted to STRING before the search. Returns an error for non-convertible types.
`str2`	Yes	STRING	The substring to search for. Same implicit conversion rules as `str1`.
`start_position`	No	BIGINT	The position in `str1` where the search starts. Default: `1` (first character). A negative value starts the search from the end of the string, where `-1` represents the last character. Returns an error for non-BIGINT types.
`nth_appearance`	No	BIGINT (> 0)	The occurrence of `str2` to locate. For example, `2` finds the second occurrence. Returns an error if the value is a non-BIGINT type or is less than or equal to `0`.

Returns a BIGINT value representing the 1-based position of str2 in str1.

Returns 0 if str2 is not found in str1.
Returns 1 if str2 is an empty string. For example, select instr('abc', '') returns 1.
Returns NULL if any of str1, str2, start_position, or nth_appearance is NULL.

Example 1: Find the position of e in Tech on the net.

-- Returns 2.
select instr('Tech on the net', 'e');

Example 2: Find the position of on in Tech on the net.

-- Returns 6.
select instr('Tech on the net', 'on');

Example 3: Find the second occurrence of e in Tech on the net, starting the search at position 3.

-- Returns 14.
select instr('Tech on the net', 'e', 3, 2);

Example 4: Search with a NULL input parameter.

-- Returns NULL.
select instr('Tech on the net', null);

INSTR is a string function. For related string search functions, see String functions.