The original in this poke
Regexp_instr
6 parameters
The first one is the string entered
The second one is the regular expression
The third is to identify a regular expression match starting from the first few characters. (Default is 1)
The fourth one is to identify the first few matching groups. (Default is 1)
The fifth is the type that specifies the return value, and if the parameter is 0, the return value is the first character of the matching position, and if the value is not 0 returns the last position of the matching value.
The sixth one is the value range:
I: case is not sensitive;
C: Case sensitive;
N: Point number. Do not match line break symbols;
M: multi-line mode;
X: Extended mode, ignoring white-space characters in regular expressions.
All test data
Sql> select * from Test_reg_substr;
A
-----------------------------------
Abc123xyz
abc123xyz456
Edward
Find numbers
Sql> SELECT
2 Regexp_instr (A, ' [0-9]+ ') as a
3 from
4 test_reg_substr;
A
----------
4
4
0
Find a number (start with the first letter, find the 1th a matching project the position of the last character)
Sql> SELECT
2 Regexp_instr (A, ' [0-9]+ ', 1) as a
3 from
4 test_reg_substr;
A
----------
7
7
0
Find a number (start with the first letter, find the 2nd matching items)
Sql> SELECT
2 Regexp_instr (A, ' [0-9]+ ') as a
3 from
4 test_reg_substr;
A
----------
0
10
0
Find a number (start with the first letter, find the 2nd a matching project the position of the last character)
Sql> SELECT
2 Regexp_instr (A, ' [0-9]+ ', 1) as a
3 from
4 test_reg_substr;
A
----------
0
13
0
Oracle Regular expression function-regexp_instr use example