[TD_SYSFNLIB.] REGEXP_INSTR ( source_string, regexp_string
[, position_arg, occurrence_arg, return_opt, match_arg ] )
Syntax Elements
- TD_SYSFNLIB
- Name of the database where the function is located.
-
source_string
- A character argument.
- If source_string is NULL, NULL is returned.
-
regexp_string
- A character argument.
- If regexp_string is NULL, NULL is returned.
-
position_arg
- A numeric argument.
-
position_arg specifies the position in source_string from which to start searching (default is 1).
- If position_arg is greater than the input string length, zero is returned.
- If position_arg is NULL, NULL is used. If position_arg is not specified, the default (1) is used.
-
occurrence_arg
- A numeric argument.
- Specifies the number of the occurrence to be returned. For example, if 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 occurrence_arg is greater than the number of matches found, 0 is returned.
- If occurrence_arg is NULL, a NULL result is returned. If occurrence_arg is omitted, the default value (1) is used.
-
return_opt
- A numeric argument.
- Valid values are:
- 0 = function returns the beginning position of the match (default).
- 1 = function returns the end position (character following the occurrence) of the match.
- If this syntax element is NULL, NULL is returned. If the syntax element is omitted, the default value (0) value is used.
-
match_arg
- 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.
- 'x' = ignore whitespace.
- If match_arg is not specified, is NULL, or is empty:
- The match is case sensitive.
- A period does not match the newline character.
-
source_string is treated as a single line.