Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Regular Expressions Functions

 

Regexp_Instr returns the location (beginning) 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) This part works like the INSTR function. The general format for the REGEXP_INSTR function with all the options is: source_string is the string in which you wish to search for the pattern. pattern_to_find is the pattern that you wish to search for in a string. position indicates where to start searching in source_string. occurrence indicates which occurrence of the pattern_to_find (in the source_string) you wish to search for. For example, which occurrence of "si" do you want to extract from the source string "Mississippi". return_option can be 0 or 1. 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. match_parameter allows you to further customize your search. "i" in match_parameter can be used for caseinsensitive matching "c" in match_parameter can be used for casesensitive 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 SQL> SQL> SELECT REGEXP_INSTR('Mississippi', 'si', 1,2,0,'i') FROM dual; REGEXP_INSTR('MISSISSIPPI','SI',1,2,0,'I') ------------------------------------------                                          7 SQL>