Oracle 10g supports regular expressions

Oracle finally provides support for regular expressions in 10G, where complex matches that need to be done by like can be simpler to implement using regular expressions.

Oracle 10g Regular expressions improve SQL flexibility. It effectively solves the problem of data validity, recognition of repetitive words, extraneous blank detection, or decomposition of multiple regular strings.

1. Oracle 10g supports the four new functions of regular expressions that use POSIX regular expressions instead of the old percent semicolon (%) and wildcard (_) characters.

(1) Regexp_like: similar to like function

(2) Regexp_instr: Similar to the function of INSTR: The number of characters returned

(3) Regexp_substr: Similar to the function of SUBSTR: intercepting a string

(4) Regexp_replace: Similar to the function of REPLACE

Use regular expressions in new functions instead of wildcard '% ' and ' _ '.

2. Regular expressions are composed of standard metacharacters (metacharacters):

' ^ ' matches the start position of the input string, used in a 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 the newline character N.

'? ' matches the preceding subexpression 0 times or once.

' + ' matches the preceding subexpression one or more times.

' * ' matches the preceding subexpression 0 or more times.

' | ' indicates a choice between the two items. Example ' ^ ([a-z]+| [0-9]+] $ ' represents a string of all lowercase letters or numbers combined.

' () ' marks the beginning and end position of a subexpression.

' [] ' marks a bracket expression.

' {m,n} ' an exact number of occurrences,m=< occurrences <=n, ' {m} ' indicates that M times, ' {m,} ' indicates that at least m times appear.

num matches num, where num is a positive integer. A reference to the match that was obtained.

3, character cluster:

[[: Alpha:]] any letter.

[[:d Igit:]] any number.

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

[[: Space:]] any white character.

[[: Upper:]] any uppercase letter.

[[: Lower:]] any lowercase letter.

[[:p UNCT:]] any punctuation.

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

4, the operation of various operators priority

(1) Escape character

(2) (), (? ：）， （？ =), [] parentheses and square brackets

(3) *, +,?, {n}, {n,}, {n,m} qualifier

(4) ^, $, anymetacharacter position and order

(5) | "or" action

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

5, example

(1) Regexp_like

/* Formatted on 2009/11/11 19:03 (Formatter Plus v4.8.8) * *

SELECT *

From Scott.emp

WHERE regexp_like (ename, ' ^a{1,3} ');

SELECT *

From Scott.emp

WHERE regexp_like (ename, ' a{1,2} ');

SELECT *

From Scott.emp

WHERE regexp_like (ename, ' ^a.*e$ ');

SELECT *

From Scott.emp

WHERE regexp_like (ename, ' ^[[:lower:]]| [[:d Igit:]];

SELECT *

From Scott.emp

WHERE regexp_like (ename, ' ^[[:lower:]];

SELECT Sal

From Scott.emp

WHERE regexp_like (Sal, ' [^[:d igit:]] ');

SELECT Sal

From Scott.emp

WHERE regexp_like (Sal, ' ^[^[:d igit:]] ";

(2) Regexp_instr

/* Formatted on 2009/11/11 19:03 (Formatter Plus v4.8.8) * *

SELECT regexp_instr (ename, ' [:d igit:]]$ ')

From Scott.emp;

SELECT Regexp_instr (MC, ' [:d igit:]]+$ ')

From Scott.emp;

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;

(3) Regexp_substr

/* Formatted on 2009/11/11 19:04 (Formatter Plus v4.8.8) * *

SELECT regexp_substr (ename, ' [a-z]+ ')

From Scott.emp;

SELECT regexp_substr (Sal, ' [0-9]+ ')

From Scott.emp;

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

From DUAL;

(4) Regexp_replace

/* Formatted on 2009/11/11 19:06 (Formatter Plus v4.8.8) * *

SELECT regexp_replace (' Joe Smith ', ' () {2,} ', ', ') as Rx_replace

From DUAL;

SELECT regexp_replace (' AA bb cc ', ' (. *） （。 *） （。 *) ', ' 3, 2, 1 '

From DUAL;