Regular Expressions for Oracle

Read Catalogue

• 1.oracle (Regular expression) Brief introduction
• 2.oracle Regular special characters
• 3.oracle Regular character clusters
• 4. Operation priority for various operators
• 5. Simulation Test examples
• 6.oracle corresponds to the regular function

Back to top 1.oracle (regular expression) Brief introduction

At present, the regular expression has been widely used in many software, including *nix (Linux, UNIX, etc.), HP and other operating systems, Php,c#,java and other development environments.
Oracle 10g Regular expressions improve SQL flexibility. Effectively solves the problem of data validity, repetition of word recognition, irrelevant blank detection, or decomposition of multiple regular strings.
The four new functions of the Oracle 10g support regular expressions are: Regexp_like, Regexp_instr, Regexp_substr, and Regexp_replace.
They use POSIX regular expressions instead of the old percent (%) and wildcard character (_) characters.

Back to top 2.oracle regular special characters

' ^ ' matches the starting position of the input string, which is used in a square bracket expression, which indicates that the character set is not accepted.
' $ ' matches the end position of the input string. If the Multiline property of the RegExp object is set, then $ also matches ' \ n ' or ' \ R '.
'. ' matches any single character except for the newline character \ n.
'? ' matches the preceding subexpression 0 or one time.
' * ' matches the preceding subexpression 0 or more times.
' + ' matches the preceding sub-expression one or more times.
' () ' marks the start and end position of a subexpression.
' [] ' marks a bracket expression.
' {m,n} ' an exact occurrence of the frequency range,m=< occurrences <=n, ' {m} ' indicates the presence of M times, ' {m,} ' indicates at least m times.
' | ' indicates a choice between the two items. Example ' ^ ([a-z]+|[ 0-9]+) $ ' represents all lowercase letters or numbers combined into a string.
\num matches num, where num is a positive integer. A reference to the obtained match.
A useful feature of regular expressions is the ability to save sub-expressions for later use, known as backreferencing. Allows for complex substitution capabilities such as adjusting a pattern to a new position or indicating the position of a substituted character or word.
The matched subexpression is stored in a temporary buffer, and the buffer is numbered from left to right, accessed by the \ number symbol.
The following example lists the names of the AA bb cc into CC, BB, aa.
Select regexp_replace (' AA bb cc ', ' (. *) ' (. *) (. *) ', ' \3, \2, \1 ') from dual; Regexp_replace (' Ellenhildismit cc, BB, aa ' \ ' turn The semantic character.

Back to top 3.oracle regular character cluster

[[: Alpha:]] any letter.
[[:d Igit:]] any number.
[[: Alnum:]] Any letters and numbers.
[[: Space:]] any whitespace character.
[[: Upper:]] any uppercase letters.
[[: Lower:]] any lowercase letter.
[[UNCT:]] any punctuation.
[[: Xdigit:]] Any 16 binary number, equivalent to [0-9a-fa-f].

Back to top 4. Operator precedence for various operators

\ escape Character
(), (?, (? =), [] parentheses and square brackets
*, +,?, {n}, {n,}, {n,m} qualifier
^, $, \anymetacharacter position and order
| "or" action

Back to top 5. Examples of simulation tests

--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 was is-is-what to find duplicate words. ');
Commit

A.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:] ');

B.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;

C.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;

D.regexp_replace
Select regexp_replace (' Joe Smith ', ' () {2,} ', ', ') as rx_replace from dual;
Select regexp_replace (' AA bb cc ', ' (. *) (. *) (. *) ', ' \3, \2, \1 ') from dual

Back to top 6.oracle corresponding to the regular function

Regexp_like.regexp_instr.regexp_substr. With Regexp_replace.

They are used in like.instr.substr and replace usages with Oracle SQL functions, but they use POSIX regular expressions instead of the old percent (%) and wildcard (_) characters.

Regexp_like is similar to the LIKE operator. If the first argument matches the regular expression, it resolves to true. For example, where Regexp_like (ename,^j[ao],i) returns a row of data in the case of ename starting with Ja or JO. I parameter specifies that the regular expression is case-sensitive. You can also specify Regexp_like in the CHECK constraint and function index. For example: "Extended information: MySQL Basic database Terminology"

^ Represents the beginning of a string that represents the end of a string. Represents the range of any character, such as [A-z], that represents any ASCII lowercase letter, and the character class "[[: Lower:]]" "equivalent? Allow a successor character to match 0 or more times + allow one successor character to match once or more * to represent 0 or more times

You can use "{m,n}" to specify an exact occurrence of the range, which means "appears from M to N times"; {m} "means" exactly M ", while" {m,} "means" at least M ". You can also use parentheses to combine a collection of characters, using the | (vertical bar) indicates replaceable. For example, the string ^ ([a-z]+|[ 0-9]+) $ will match all strings that are combined by lowercase letters or numbers.

Regular Expressions for Oracle