REGEXP_INSTR
Returns the starting or ending position in a string where a regular expression matches. This function returns 0 if no match for the regular expression is found in the string.
Syntax
REGEXP_INSTR( string, pattern [, position [, occurrence ... [, return_position [, regexp_modifier ] ... [, captured_subexp ] ] ] ] )
Parameters
string
|
The |
||||||||||||
pattern
|
The regular expression to search for within the string. The syntax of the regular expression is compatible with the Perl 5 regular expression syntax. See the Perl Regular Expressions Documentation for details. |
||||||||||||
position
|
[Optional] The number of characters from the start of the string where the function should start searching for matches. By default, the function begins searching for a match at the first (leftmost) character. Setting this parameter to a value greater than 1 begins searching for a match at the nth character you specify. Default value: 1 |
||||||||||||
occurrence
|
[Optional] Controls which occurrence of a pattern match in the string to return. By default, the function returns the position of the first matching substring. Use this parameter to find the position of subsequent matching substrings. For example, setting this parameter to 3 returns the position of the third substring that matches the pattern. Default value: 1 |
||||||||||||
return_position
|
[Optional] Sets the position within the string to return. Using the default position (0), the function returns the string position of the first character of the substring that matches the pattern. If you set return_position to 1, the function returns the position of the first character after the end of the matching substring. Default value: 0 |
||||||||||||
regexp_modifier
|
[Optional] One or more single-character flags that modify how the regular expression finds matches in string:
|
||||||||||||
captured_subexp
|
[Optional] The captured subexpression whose position to return. By default, the function returns the position of the first character in string that matches the regular expression. If you set this value from 1 – 9, the function returns the subexpression captured by the corresponding set of parentheses in the regular expression. For example, setting this value to 3 returns the substring captured by the third set of parentheses in the regular expression. Default value: 0 Note: The subexpressions are numbered left to right, based on the appearance of opening parenthesis, so nested regular expressions . For example, in the regular expression |
Notes
This function operates on UTF-8 strings using the default locale, even if the locale has been set to something else.
If you are porting a regular expression query from an Oracle database, remember that Oracle considers a zero-length string to be equivalent to NULL, while Vertica does not.
Examples
Find the first occurrence of a sequence of letters starting with the letter e
and ending with the letter y
in the phrase "easy come, easy go."
=> SELECT REGEXP_INSTR('easy come, easy go','e\w*y'); REGEXP_INSTR -------------- 1 (1 row)
Find the first sequence of letters starting with the letter e
and ending with the letter y
in the string "easy come, easy go" starting at the second character (2
) ."
=> SELECT REGEXP_INSTR('easy come, easy go','e\w*y',2); REGEXP_INSTR -------------- 12 (1 row)
Find the second sequence of letters starting with the letter e
and ending with the letter y
in the string "easy come, easy go" starting at the first character.
=> SELECT REGEXP_INSTR('easy come, easy go','e\w*y',1,2); REGEXP_INSTR -------------- 12 (1 row)
Find the position of the first character after the first whitespace in the string "easy come, easy go."
=> SELECT REGEXP_INSTR('easy come, easy go','\s',1,1,1); REGEXP_INSTR -------------- 6 (1 row)
Find the position of the start of the third word in a string by capturing each word as a subexpression, and returning the third subexpression's start position.
=> SELECT REGEXP_INSTR('one two three','(\w+)\s+(\w+)\s+(\w+)', 1,1,0,'',3); REGEXP_INSTR -------------- 9 (1 row)