INSTR - MaxCompute - Alibaba Cloud Documentation Center

Returns the position of substring str2 in string str1.

Syntax

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

str1: required. A value of the STRING type. This parameter specifies the string that contains the substring you want to search for. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted into the STRING type before calculation. If the input value cannot be converted into the STRING type, an error is returned.
str2: required. A value of the STRING type. This parameter specifies the substring that you want to search for. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted into the STRING type before calculation. If the input value cannot be converted into the STRING type, an error is returned.
start_position: optional. A value of the BIGINT type. If the input value is of another data type, an error is returned. This parameter specifies the position of the character in str1 from which the search starts. The default start position is the first character, marked as 1. If start_position is a negative value, the search starts from the end to the start of the string and the last character is -1.
nth_appearance: optional. A value of the BIGINT type, which is greater than 0. This parameter specifies the position of substring str2 that appears in string str1 at the nth time. If the value of nth_appearance is of another data type or is less than or equal to 0, an error is returned.

A value of the BIGINT type is returned. The return value varies based on the following rules:

If str2 is not found in str1, the value 0 is returned.
If str2 is an empty string, the matching always succeeds. For example, 1 is returned for select instr('abc','');.
If the value of str1, str2, start_position, or nth_appearance is null, null is returned.

Example 1: Return the position of the substring e in the string Tech on the net. Sample statement:
```
-- The return value is 2. 
select instr('Tech on the net', 'e');
```
Example 2: Return the position of substring on in string Tech on the net. Sample statement:
```
-- The return value is 6. 
select instr('Tech on the net', 'on');
```
Example 3: Return the position of the second occurrence in which the substring e appears in the string Tech on the net from the third character. Sample statement:
```
-- The return value is 14. 
select instr('Tech on the net', 'e', 3, 2);
```

Example 4: An input parameter is set to null. Sample statement:

-- The return value is null. 
select instr('Tech on the net', null);

INSTR is a string function. For more information about functions related to string searches and conversion, see String functions.