Regular Expression

a character argument.
If source_string is NULL, NULL is returned.

a character argument.
If regexp_string is NULL, NULL is returned.

a numeric argument.
The position_arg specifies the position in the source_string from which to start searching (default is 1).
If the position_arg is greater than the input string length, NULL is returned.
If the position_arg is NULL, the default value (1) is used.

a numeric argument.
The occurrence_arg specifies the number of the occurrence to be returned (default is 1). For example, if the occurrence_arg is 2, the function matches the first occurrence in source_string and starts searching from the character following the first occurrence in source_string for the second occurrence in source_string.
If the occurrence_arg is greater than the number of matches found, NULL is returned.
If occurrence_arg is NULL, the default value (1) is used.

a character argument.
Valid values are:
'i' = case-insensitive matching.
'c' = case sensitive matching.
'n' = the period character (match any character) can match the newline character.
'm' = source_string is treated as multiple lines instead of as a single line. With this option, the '^' and '$' characters apply to each line in source_string instead of the entire source_string.
'l' = if source_string exceeds the current maximum allowed source_string size (currently 16 MB), a NULL is returned instead of an error. This is useful for long-running queries where you do not want long strings causing an error that would make the query fail.
If the match_arg is not specified:
• The match is case sensitive.
• A period does not match the newline character.
• source_string is treated as a single line.
If there is no match, NULL is returned.
If match_arg is not valid, an error is returned.

• source_string = CHAR, VARCHAR, or CLOB
• regexp_string = CHAR or VARCHAR (maximum size of 512 B)
• position_arg = NUMBER
• occurrence_arg = NUMBER
• match_arg = VARCHAR

• CHAR, VARCHAR returns VARCHAR in the same character set as source_string.
• CLOB returns CLOB in the same character set as source_string.

SELECT REGEXP_SUBSTR('mint chocolate chip', 'ch(i|o)p', 1, 1, 'c');

SELECT REGEXP_SUBSTR('mint chocolate chip chop', ' ch(i|o)p', 1, 2, 'i');

(“空集”) ∅指示集合∅
(“空串”) ε指示集合{ε}
(“文字字符”)在Σ中的a指示集合{a}

(“串接”) RS指示集合{ αβ | α ∈ R，β ∈ S }。例如：{"ab","c"}{"d","ef"} = {"abd", "abef", "cd", "cef"}。
(“选择”) R|S指示R和S的并集。例如：{"ab", "c"}|{"ab", "d", "ef"}= {"ab", "c", "d", "ef"}
(“Kleene星号”) R* 指示包含ε并且闭包在字符串串接下的R的最小超集。这是可以通过R中的零或多个字符串的串接得到所有字符串的集合。例如，{"ab", "c"}* = {ε, "ab", "c", "abab", "abc", "cab", "cc", "ababab", ... }。

a|b*指示{ε, a, b, bb, bbb, ...}。

(a|b)*指示由包括空串、任意数目个a或b字符组成的所有字符串的集合。

ab*(c|ε)指示开始于一个a接着零或多个b和最终可选的一个c的字符串的集合。

+加号代表前面的字符必须至少出现一次。（1次、或多次）。例如，「goo+gle」可以匹配google、gooogle、goooogle等;

?问号代表前面的字符最多只可以出现一次。（0次、或1次）。例如，「colou?r」可以匹配color或者colour;

*星号代表前面的字符可以不出现，也可以出现一次或者多次。（0次、或1次、或多次）。例如，「0*42」可以匹配42、042、0042、00042等。