This function returns the starting position of a pattern, so it works much like the familiar INSTR function
Syntax:
REGEXP_INSTR(source_string, pattern[, start_position[, occurrence[, return_option[, match_parameter]]]])
source_string - can be column of CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB but not LONG datatypes
start_position - Optionally, indicate the start_position you want to begin the search
occurrence - The occurrence parameter defaults to 1 unless you indicate that you are looking for a subsequent occurrence
return_option - The default value of the return_option is 0, which returns the starting position of the pattern.A value of 1 returns the starting position of the next character following the match
Example:
The following SQL query return's the starting position of the five-digit zip-code pattern within the string Joe Smith, 10045 Berry Lane, San Joseph, CA 91234.
Expand|Select|Wrap|Line Numbers
- SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
- '[[:digit:]]{5}$')
- AS rx_instr
- FROM dual
- RX_INSTR
- ----------
- 45