REGEXP_INSTR - MaxCompute - Alibaba Cloud Documentation Center

Returns the start or end position of the substring that matches pattern at the nth occurrence specified by occurrence, in the source string from the start position specified by start_position.

Syntax

bigint regexp_instr(string <source>, string <pattern>[,bigint <start_position>[, bigint <occurrence>[, bigint <return_option>]]])

Parameters

source: required. A value of the STRING type. This parameter specifies the source string.
pattern: required. A constant of the STRING type or a regular expression. This parameter specifies the pattern that a specified substring must match. For more information about regular expressions, see Regular expressions. If pattern is an empty string, an error is returned.
start_position: optional. A constant of the BIGINT type. This parameter specifies the position from which the search starts. If you do not specify this parameter, the default value 1 is used.
occurrence: optional. A constant of the BIGINT type. If you do not specify this parameter, the default value 1 is used. This value indicates the position where a substring matches the pattern in the search for the first time.
return_option: optional. A constant of the BIGINT type. This parameter specifies whether the start or end position of the substring that matches a specified pattern is returned. Valid values: 0 and 1. If this parameter is not specified, the default value 0 is used. If this parameter is set to an invalid number or a value of another data type, an error is returned. The value 0 indicates that the start position of the substring that matches a specified pattern is returned. The value 1 indicates that the end position of the substring that matches a specified pattern is returned.

Return value

A value of the BIGINT type is returned. The return value is the start or end position specified by return_option of the matched substring in the source string. The return value varies based on the following rules:

If pattern is an empty string, an error is returned.
If the value of start_position or occurrence is not of the BIGINT type or is less than or equal to 0, an error is returned.
If the value of source, pattern, start_position, occurrence, or return_option is null, null is returned.

Examples

Example 1: Return the start position of the substring that matches o[[:alpha:]]{1} at the second occurrence in the i love www.taobao.com string from the third character. Sample statement:
```
-- The return value is 14. 
select regexp_instr('i love www.taobao.com', 'o[[:alpha:]]{1}', 3, 2);
```
Example 2: Return the end position of the substring that matches o[[:alpha:]]{1} at the second occurrence in the i love www.taobao.com string from the third character. Sample statement:
```
-- The return value is 16. 
select regexp_instr('i love www.taobao.com', 'o[[:alpha:]]{1}', 3, 2, 1);
```

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

-- The return value is null. 
select regexp_instr('i love www.taobao.com', null, 3, 2);

Related functions

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