10g REGULAR EXPRESSIONS (REGEXP_INSTR)

[amitpatel66]

REGEXP_INSTR

This function returns the starting position of a pattern, so it works much like the familiar INSTR function

Syntax:

REGEXP_INSTR(so urce_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.

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

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.