Oracle SQL - REGEXP_INSTR function

  1. HOME
  2. Oracle SQL
  3. Function
  4. 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>-- from  www. ja  v a2  s  .c om

Related Topics

Quiz