Summary
: in this tutorial, you will learn how to use the Oracle
REGEXP_INSTR()
function to search for a substring in a string using a regular expression pattern.
Introduction to Oracle
REGEXP_INSTR()
function
The
REGEXP_INSTR()
function enhances the functionality of the
INSTR()
function by allowing you to search for a substring in a string using a regular expression pattern.
The following illustrates the syntax of the
REGEXP_INSTR()
function:
REGEXP_INSTR(
string,
pattern,
position,
occurrence,
return_option,
match_parameter
)
Code language: SQL (Structured Query Language) (sql)
The
REGEXP_INSTR()
function evaluates the
string
based on the
pattern
and returns an integer indicating the beginning or ending position of the matched substring, depending on the value of the
return_option
argument. If the function does not find any match, it will return 0.
Here is the detail of each argument:
string
(mandatory)
Is the string to search.
pattern
(mandatory)
Is a regular expression to be matched.
The maximum size of the
pattern
is 512 bytes. The function will convert the type of the
pattern
to the type of the
string
if the types of
pattern
and
string
are different.
position
(optional)
Is a positive integer that determines the starting position in the string that the function begins the search.
The position defaults to 1, meaning that the function starts searching at the beginning of the string.
occurrence
(optional)
Is a positive integer that determines for which occurrence of the
pattern
in the
string
the function should search. By default, the occurrence is 1, meaning that the function searches for the first occurrence of
pattern
.
return_option
(optional)
The
return_option
can be 0 and 1. If
return_option
is 0, the function will return the position of the first character of the occurrence.
Otherwise, it returns the position of the character following the occurrence.
By default,
return_option
is 0.
match_parameter
(optional)
Specify the default matching behavior of the function. The
match_parameter
accepts the values listed in the following table:
Value | Description |
---|---|
‘c’ | Performs case-sensitive matching. |
‘i’ | Performs case-insensitive matching. |
‘n’ | Allows the period (.), which is the match-any-character character, to match the newline character. If you skip this parameter, then the period (.) does not match the newline character. |
‘m’ |
The function treats the
string
as multiple lines. The function interprets the caret (
^
) and the dollar sign (
$
) as the start and end, respectively, of any line anywhere in the
string
, rather than only at the start or end of the entire
string
. If you skip this parameter, then function treats the source string as a single line.
|
‘x’ | Ignores whitespace characters. By default, whitespace characters match themselves. |
Oracle
REGEXP_INSTR()
function examples
This regular expression matches any 11-12 digit phone number with optional group characters and (+) sign at the beginning:
(\+?( |-|\.)?\d{1,2}( |-|\.)?)?(\(?\d{3}\)?|\d{3})( |-|\.)?(\d{3}( |-|\.)?\d{4})
Code language: SQL (Structured Query Language) (sql)
Here is the explanation of the regular expression:
The following example uses the above regular expression to search for the first occurrence of a phone number in the string
'If you have any question please call 123-456-7890 or (123)-456-7891'
:
SELECT
REGEXP_INSTR(
'If you have any question please call 123-456-7890 or (123)-456-7891',
'(\+?( |-|\.)?\d{1,2}( |-|\.)?)?(\(?\d{3}\)?|\d{3})( |-|\.)?(\d{3}( |-|\.)?\d{4})') First_Phone_No
dual;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
FIRST_PHONE_NO
---------------
Code language: SQL (Structured Query Language) (sql)
To find the second phone number in the string, you pass the
position
and
occurrence
arguments to the
REGEXP_INSTR()
function as follows:
SELECT
REGEXP_INSTR(
'If you have any question please call 123-456-7890 or (123)-456-7891',
'(\+?( |-|\.)?\d{1,2}( |-|\.)?)?(\(?\d{3}\)?|\d{3})( |-|\.)?(\d{3}( |-|\.)?\d{4})',
1) Second_Phone_No