REGEXP_INSTR Examples

REGEXP_INSTR Examples
======================
In Oracle 10g
=============
regexp_instr (string, pattern)
regexp_instr (string, pattern, position)
regexp_instr (string, pattern, position, occurence)
regexp_instr (string, pattern, position, occurence, return-option)
regexp_instr (string, pattern, position, occurence, return-option, parameters)

----------------------------------------------------------------
In Oracle 11g
=============
regexp_instr (string, pattern, position, occurence, return-option, parameters,subexpr)

subexpr:
REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits
the pattern match to a specific subexpression in the search pattern.

---------------------------------------------------------------

1. source_string is a character expression that serves as the search value.
Pattern is the regular expression. It is usually a text literal .It can contain up to 512 bytes.

2. Pattern is the regular expression. It is usually a text literal .It can contain up to 512 bytes.

For more about pattern:

http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10759/ap_posix001.htm

3. position is a positive integer indicating the character of source_string where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_string.

4. occurrence is a positive integer indicating which occurrence of pattern in source_string Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern.

5. return_option lets you specify what Oracle should return in relation to the occurrence:
If you specify 0, then Oracle returns the position of the first character of the occurrence. This is the default.
If you specify 1, then Oracle returns the position of the character following the occurrence.

6. match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:

Parameters
parameters can be a combination of

i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
x: to ignore white spaces.

7. subexpr:
REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits
the pattern match to a specific subexpression in the search pattern.


Examples

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

SELECT
REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
'[^ ]+', 1, 6) "REGEXP_INSTR"
FROM DUAL;

REGEXP_INSTR
------------
37
The following example examines the string, looking for occurrences of words beginning with s, r, or p, regardless of case, followed by any six alphabetic characters. Oracle begins searching at the third character in the string and returns the position in the string of the character following the second occurrence of a seven-letter word beginning with s, r, or p, regardless of case.

SELECT
REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
'[s|r|p][[:alpha:]]{6}', 3, 2, 1, 'i') "REGEXP_INSTR"
FROM DUAL;

REGEXP_INSTR
------------
28


Examples in Oracle 11g only
===========================

The following examples use the subexpr argument to search for a particular subexpression in pattern. The first statement returns the position in the source string of the first character in the first subexpression, which is '123':

SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1)
"REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
-------------------
1
The next statement returns the position in the source string of the first character in the second subexpression, which is '45678':

SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2)
"REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
-------------------
4
The next statement returns the position in the source string of the first character in the fourth subexpression, which is '78':

SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4)
"REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
-------------------
7