Top

Name

regexp_instr — Returns the first position of the occurrence of a regular-expression pattern in a given string.

Synopsis

integer regexp_instr ( in source_string varchar ,
  in pattern varchar ,
  in start_position integer ,
  in occurrence integer ,
  in return_option integer ,
  in match_parameter integer );
 

Description

This function looks for a pattern and returns the first position of the pattern. Optionally, you can indicate the start_position you want to begin the search. The occurrence parameter defaults to 1 unless you indicate that you are looking for a subsequent occurrence. 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.

This function returns the starting position of a pattern, so it works much like the familiar INSTR function. The main difference between the two functions is that REGEXP_INSTR lets you specify a pattern instead of a specific search string; thus providing greater versatility.

Parameters

source_string

The string to search.

pattern

The regular expression to match.

start_position

The position to begin the search from.

occurrence

Defaulting to 1, this parameter describes how many occurrences to look for.

return_option

Default 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.

match_parameter

Match parameter value.

Returns

This function returns the a substring matching the regular expression.

Examples

Example 24.311. Examples

This example uses REGEXP_INSTR() to return the starting position of the five-digit zip-code pattern within the string Joe Smith, 10045 Berry Lane, San Joseph, CA 91234. If the regular expression is written as :digit:?{5}, you will get the house number's starting position instead of the zip code's, because 10045 is the first occurrence of five consecutive digits. Therefore, you must anchor the expression to the end of the line, as indicated with the $ metacharacter, and the function will display the starting position of the zip code regardless of the number of digits for the house number. 

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
       '[[:digit:]]{5}\$')
       AS rx_instr;
  RX_INSTR
----------
        45

[Note] Compatibility:

The regular expressions supported here follow version 7.9 of the Perl Compatible Regular Expression (PCRE) syntax.

  1. Wikipedia

  2. PCRE.org

Prev  Up  Next
regexp_replace   Home  regexp_like