Oracle Regular expression function-regexp_substr use example

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