Oracle REGEXP_INSTR function

Description

The Oracle REGEXP_INSTR function is used to return the location of a regular expression pattern in a string. This function allows you to find a substring in a string using regular expression pattern matching. If no match is found, then the function returns 0.

Uses of Oracle REGEXP_INSTR Function

• Locate Substring: Identify the position of a substring within a string using regular expressions.


• Pattern Matching: Use various regular expression patterns to find specific character sequences.


• Custom Search Position: Specify a starting position within the string for the search.


• Multiple Occurrences: Search for the nth occurrence of a pattern within the string.


• Return Options: Choose whether to return the position of the first character of the match or the character following the match.


• Subexpression Targeting: Retrieve the position of specific subexpressions within the matched pattern.


• Case Sensitivity: Perform case-sensitive or case-insensitive searches based on match parameters.


• Multiline Support: Handle multi-line strings with appropriate match parameters.


• Whitespace Handling: Include or ignore whitespace characters in pattern matching.

Syntax:

REGEXP_INSTR (source_char, pattern
              [, position
                 [, occurrence
                    [, return_opt
                       [, match_param
                          [, subexpr]
                       ]
                    ]
                 ]
              ]

Parameter:

Here is the list of pattern formatting operator:

Here is the list of Regular Expression Operators and Metasymbols:

Here is the list of values for match_param:

Applies to

Oracle 12c, Oracle 11g

Examples: Oracle REGEXP_INSTR function

The following example findS the position of the first 't' character in a string.

SELECT REGEXP_INSTR ('The web development Tutorial', 't') FROM dual;

Sample Output:

REGEXP_INSTR('THEWEBDEVELOPMENTTUTORIAL','T')
---------------------------------------------
                                           19

The above example will return 2 because it is counting the number of occurrences of 't' in the string. Here we did not specify a match_parameter value, the REGEXP_COUNT function will perform a case-sensitive search which means that the 'T' characters will not be included in the count.

In the following example, we have provided start_position of 1, an occurrence of 1, a return_option of 0, and a match_parameter of 'i', the query will return 1 as the result. This time, the function will search for both 't' and 'T' values and return the first occurrence.

SELECT REGEXP_INSTR ('The web development Tutorial', 't',1,1,0,'i') FROM dual;

Sample Output:

REGEXP_INSTR('THEWEBDEVELOPMENTTUTORIAL','T',1,1,0,'I')
-------------------------------------------------------
                                                      1

The following example examines the string, looking for occurrences of one or more non-blank characters. Searching started at the first character in the string and returns the starting position (default) of the third occurrence of one or more non-blank characters.

SELECT
REGEXP_INSTR('The web development tutorial',
'[^ ]+', 1, 3) "REGEXP_INSTR"
FROM DUAL;

Sample Output:

REGEXP_INSTR
------------
           9

The following example examines the string, looking for occurrences of words beginning with t,e, or w, regardless of case, followed by any three alphabetic characters .Searching begins at the third character in the string and returns the position in the string of the character following the second occurrence with t,e, or w, regardless of case.

SELECT
REGEXP_INSTR('The web development tutorial',
'[s|r|p][[:alpha:]]{3}', 3, 2, 1, 'i') "REGEXP_INSTR"
FROM DUAL;

Sample Output:

REGEXP_INSTR
------------
          29

Previous: REGEXP_COUNT
Next: NLS_CHARSET_DECL_LEN