Regexp_Instr returns the location (beginning) of a pattern in a given string : REGEXP_INSTR « Regular Expressions Functions « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Regular Expressions Functions
  3. REGEXP_INSTR

REGEXP_INSTR extends the regular INSTR string function by allowing searches of regular expressions.

The simplest form of this function is:

REGEXP_INSTR(source_string, pattern_to_find)

This part works like the INSTR function.

The general format for the REGEXP_INSTR function with all the options is:

  1. source_string is the string in which you wish to search for the pattern.
  2. pattern_to_find is the pattern that you wish to search for in a string.
  3. position indicates where to start searching in source_string.
  4. occurrence indicates which occurrence of the pattern_to_find (in the source_string) you wish to search for.
  5. For example, which occurrence of "si" do you want to extract from the source string "Mississippi".
  6. return_option can be 0 or 1.
  7. If return_option is 0, Oracle returns the first character of the occurrence (this is the default);
  8. if return_option is 1, Oracle returns the position of the character following the occurrence.
  9. match_parameter allows you to further customize your search.
  10. "i" in match_parameter can be used for caseinsensitive matching
  11. "c" in match_parameter can be used for casesensitive matching
  12. "n" in match_parameter allows the period to match the new line character
  13. "m" in match_parameter allows for more than one line in source_string
SQL>
SQL> SELECT REGEXP_INSTR('Mississippi', 'si', 1,2,0,'i') FROM dual;

REGEXP_INSTR('MISSISSIPPI','SI',1,2,0,'I')
------------------------------------------
                                         7

SQL>








18.2.REGEXP_INSTR
18.2.1.Regexp_Instr returns the location (beginning) of a pattern in a given string
18.2.2.REGEXP_INSTR(x, pattern [, start [, occurrence [, return_option [, match_option]]]]) searches for pattern in x.
18.2.3.Regexp_Instr
18.2.4.Parameters is a field that may be used to define how one wants the search to proceed:
18.2.5.Find the 's' and ignore case.
18.2.6.The simplest regular expression matches letters, letter for letter
18.2.7.SELECT REGEXP_INSTR('Two is bigger than One','One') where_it_is
18.2.8.Occurrence refers to the first, second, third, etc., occurrence of the pattern in S. The default is 1 (first).
18.2.9.Return-option returns the position of the start or end of the matched string.
18.2.10.The Return-option is set to 1 to indicate the end of the found pattern
18.2.11.Returns the position of the second occurrence that matches the regular expression s[[:alpha:]]{3} starting at position 1:
18.2.12.Returns the position of the second occurrence that matches the letter o starting at position 10 using REGEXP_INSTR()
18.2.13.Specify any series of letters and find matches, just like INSTR
18.2.14.regexp_instr(comments, '[^ ]+', 1, 9) > 0