Oracle SQL - REGEXP_INSTR function

Introduction

REGEXP_INSTR function returns the starting location of a pattern in a given string.

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) 

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

REGEXP_INSTR(source_string, pattern_to_find [, position, 
   occurrence, return_option, match_parameter]) 
  • source_string is the string to search for.
  • pattern_to_find is the pattern to search for in a string.
  • position sets where to start searching.
  • occurrence indicates which occurrence of the pattern_to_find you wish to search for.
  • return_option can be 0 or 1.
  • match_parameter allows you to further customize your search.

For return_option:

  • If return_option is 0, Oracle returns the first character of the occurrence (this is the default);
  • if return_option is 1, Oracle returns the position of the character following the occurrence.

For match_parameter

  • "i" in match_parameter can be used for case-insensitive matching
  • "c" in match_parameter can be used for case-sensitive matching
  • "n" in match_parameter allows the period to match the new line character
  • "m" in match_parameter allows for more than one line in source_string

Demo

SQL>
SQL> SELECT REGEXP_INSTR('Mississippi', 'si', 1,2,0,'i') FROM dual;

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

SQL>--   w ww .  j a  v a 2s .  c o  m

Related Topics

Quiz