ORACLE Common Regular Expression detailed explanation

Oracle uses regular expressions without these 4 functions:

1. Regexp_like

2. Regexp_substr

3. Regexp_instr

4. Regexp_replace

Look at the function name and guess what's the use.

Regexp_like can only be used for conditional expressions, similar to like, but using regular expressions to match, the syntax is simple:

The REGEXP_SUBSTR function, similar to the substr, is used to pick up the string of words subcode the Single-character expression description, and the syntax is as follows:

The REGEXP_INSTR function, similar to the InStr, is used to calibrate the starting position of a string of strings that match a regular expression, as follows:

The Regexp_replace function, similar to replace, replaces a string that matches a regular expression, and the syntax is as follows:

Here are some of the implications of the parameters:

1. Source_char, enter a string that can be a column name or a string constant, variable.

2. pattern, regular expression.

3. Match_parameter, matching options.

Value range: I: case insensitive; C: Case sensitive; n: Dot number. does not match newline symbols; m: multiline mode; x: Extended mode, ignoring whitespace characters in regular expressions.

4. Position, identifies a regular expression match from the beginning of the first few characters.

5. Occurrence, identifies the first few matching groups.

6. Replace_string, replaced by a string.

' Escape character.

Character clusters:

[[: Alpha:]] any letter.

[[:d Igit:]] any number.

[[: Alnum:]] any letter or number.

[[: Space:]] any white character.

[[: Upper:]] any uppercase letter.

[[: Lower:]] any lowercase letter.

[[UNCT:]] any punctuation.

[[: Xdigit:]] Any number in 16, equivalent to [0-9a-fa-f].

Operation Precedence for various operators

Escape character

(), (?, (? =), [] parentheses and square brackets

*, +,?, {n}, {n,}, {n,m} qualifier

^, $, anymetacharacter position and order

| "or" action

--Test data

CREATE TABLE Test (MC VARCHAR2 (60));

INSERT into test values (' 112233445566778899 ');

INSERT into test values (' 22113344 5566778899 ');

INSERT into test values (' 33112244 5566778899 ');

INSERT into test values (' 44112233 5566 778899 ');

INSERT into test values (' 5511 2233 4466778899 ');

INSERT into test values (' 661122334455778899 ');

INSERT into test values (' 771122334455668899 ');

INSERT into test values (' 881122334455667799 ');

INSERT into test values (' 991122334455667788 ');

INSERT into test values (' Aabbccddee ');

INSERT into test values (' Bbaaaccddee ');

INSERT into test values (' Ccabbddee ');

INSERT into test values (' Ddaabbccee ');

INSERT into test values (' EEAABBCCDD ');

INSERT into test values (' ab123 ');

INSERT into test values (' 123xy ');

INSERT into test values (' 007ab ');

INSERT into test values (' Abcxy ');

INSERT into test values (' The final test being ' how to find duplicate words. ');

Commit

First, Regexp_like

SELECT * FROM Test where regexp_like (MC, ' ^a{1,3} ');

SELECT * FROM Test where regexp_like (MC, ' a{1,3} ');

SELECT * FROM Test where regexp_like (MC, ' ^a.*e$ ');

SELECT * FROM Test where regexp_like (MC, ' ^[[:lower:]]|[ [:d Igit:]];

SELECT * FROM Test where regexp_like (MC, ' ^[[:lower:] ');

Select MC from Test Where regexp_like (MC, ' [^[:d igit:]] ');

Select MC from Test Where regexp_like (MC, ' ^[^[:d igit:]] ');

Second, Regexp_instr

Select Regexp_instr (MC, ' [:d igit:]]$ ') from test;

Select Regexp_instr (MC, ' [:d igit:]]+$ ') from test;

Select regexp_instr (' The price is $. ', ' $[[:d igit:]]+ ') from DUAL;

Select regexp_instr (' Onetwothree ', ' [^[[:lower:]]] ') from DUAL;

Select regexp_instr (',,,,, ', ' [^,]* ') from DUAL;

Select regexp_instr (',,,,, ', ' [^,] ') from DUAL;

Third, Regexp_substr

SELECT Regexp_substr (MC, ' [a-z]+ ') from test;

SELECT Regexp_substr (MC, ' [0-9]+ ') from test;

SELECT regexp_substr (' aababcde ', ' ^a.*b ') from DUAL;

Four, Regexp_replace

Select regexp_replace (' Joe Smith ', ' () {2,} ', ', ') as rx_replace from dual;

Select regexp_replace (' AA bb cc ', ' (. *) (. *) (. *) (. *) (. *) ', ' 3, 2, 1 ') from dual;

Note : Please pay attention to the triple Programming Tutorials section for more wonderful articles .