REGEXP_SUBSTR command format, parameters, and return value-MaxCompute(MaxCompute)-阿里云帮助中心

Returns the substring that matches a regular expression pattern at the nth occurrence in a source string, starting from a specified position.

Syntax

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

Parameters

Required:

source: The STRING value to search.
pattern: A STRING constant or regular expression that defines the pattern to match. For syntax details, see Regular expressions.

Optional:

start_position: A BIGINT constant specifying where the search begins. Must be greater than 0. Default: 1 (search starts at the first character)
occurrence: A BIGINT constant specifying which match to return. Must be greater than 0. Default: 1 (returns the first match)

Return value

Returns a STRING value.

Condition	Return value
A match is found	The matched substring
No match found	`null` (not an empty string)
`pattern` is an empty string	Error
`start_position` or `occurrence` is not BIGINT, or is less than or equal to 0	Error
Any parameter is `null`	`null`

Examples

Extract a pattern match from a string

-- Returns: aliyun
select regexp_substr('I love aliyun very much', 'a[[:alpha:]]{5}');

-- Returns: have  (first word preceded by a space, occurrence=1)
select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1);

-- Returns: 2  (second word preceded by a space, occurrence=2)
select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 2);

Handle null input

-- Returns: null
select regexp_substr('I love aliyun very much', null);

Related functions

REGEXP_SUBSTR is a string function. For other string search and conversion functions, see String functions.