The original in this poke
Regexp_substr
5 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 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
<Name>Edward</Name>
Retrieving the median number
Sql> SELECT
2 Regexp_substr (A, ' [0-9]+ ')
3 from
4 TEST_REG_SUBSTR
5 WHERE
6 Regexp_like (A, ' [0-9]+ ');
Regexp_substr (A, ' [0-9]+ ')
---------------------------------
123
123
Retrieves the median number (starting with the first letter, finding the 2nd match)
Sql> SELECT
2 NVL (Regexp_substr (A, ' [0-9]+ ', 1, 2), '-') as a
3 from
4 TEST_REG_SUBSTR
5 WHERE
6 Regexp_like (A, ' [0-9]+ ');
A
------------------------------------------------------
-
456
Get "Character Set fit"
Sql> SELECT
2 Regexp_substr (A, ' \w+ ')
3 from
4 TEST_REG_SUBSTR
5 WHERE
6 Regexp_like (A, ' \w+ ');
Regexp_substr (A, ' \w+ ')
-------------------------------
Abc123xyz
abc123xyz456
Name
Get a "character set" (starting with the first letter and finding the 2nd match)
Sql> SELECT
2 NVL (Regexp_substr (A, ' \w+ ', 1, 2), '-') as a
3 from
4 TEST_REG_SUBSTR
5 WHERE
6 Regexp_like (A, ' \w+ ');
A
---------------------------------------------------
-
-
Edward
Oracle Regular expression function-regexp_substr use example