ORACLE RegExp Application Example

There are four main functions in Oracle that support regular expressions:
1,regexp_like: Similar to the function of like
2,REGEXP_INSTR: Similar to the function of INSTR
3,REGEXP_SUBSTR: Similar to the function of SUBSTR
4,regexp_replace: Similar to the function of REPLACE
They are used in the same way as Oracle SQL functions like, INSTR, SUBSTR, and replace usages.
However, they use POSIX regular expressions instead of the old percent (%) and wildcard character (_) characters.
POSIX Regular expressions are composed of standard metacharacters (metacharacters):
' ^ ' 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, the $ is also
With ' \ n ' or ' \ R '.
'. ' matches any single character except for a newline character.
'? ' matches the preceding subexpression 0 or one time.
' + ' matches the preceding sub-expression one or more times.
' * ' matches the preceding subexpression 0 or more times.
' | ' indicates a choice between the two items. Example ' ^ ([a-z]+|[ 0-9]+) $ ' denotes all lowercase letters or numbers combined into a
String.
' () ' 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,} ' means at least
Appears m times.
\num matches num, where num is a positive integer. A reference to the obtained match.
Character clusters:
[[: Alpha:]] any letter.
[[:d Igit:]] any number.
[[: Alnum:]] Any letters and numbers.
[[: Space:]] any whitespace character.
[[: Upper:]] any uppercase letters.
[[: Lower:]] any lowercase letter.
[[:p UNCT:]] any punctuation.
[[: Xdigit:]] Any 16 binary number, equivalent to [0-9a-fa-f].
Operator precedence for various operators
\ escape Character
(), (?:), (? =), [] parentheses and square brackets
*, +,?, {n}, {n,}, {n,m} qualifier
^, $, anymetacharacter position and order
|
*/
--Create a table
CREATE TABLE Fzq
(
ID varchar (4),
Value varchar (10)
);
--Data insertion
INSERT INTO FZQ values
(' 1 ', ' 1234560 ');
INSERT INTO FZQ values
(' 2 ', ' 1234560 ');
INSERT INTO FZQ values
(' 3 ', ' 1b3b560 ');
INSERT INTO FZQ values
(' 4 ', ' abc ');
INSERT INTO FZQ values
(' 5 ', ' ABCDE ');
INSERT INTO FZQ values
(' 6 ', ' adreasx ');
INSERT INTO FZQ values
(' 7 ', ' 123 45 ');
INSERT INTO FZQ values
(' 8 ', ' ADC de ');
INSERT INTO FZQ values
(' 9 ', ' adc,.de ');
INSERT INTO FZQ values
(' Ten ', ' 1 B ');
INSERT INTO FZQ values
(' Ten ', ' Abcbvbnb ');
INSERT INTO FZQ values
(' 11 ', ' 11114560 ');
INSERT INTO FZQ values
(' 11 ', ' 11124560 ');
--regexp_like
--query value with a record ending at 1 in 60 and a length of 7 bits
SELECT * from fzq where value like ' 1____60 ';
SELECT * from Fzq where regexp_like (value, ' 1....60 ');
--Query the record of value ending with 1 in 60 and the length is 7 bits and all is a number of records.
-Using like is not a good implementation.
SELECT * from Fzq where regexp_like (value, ' 1[0-9]{4}60 ');
--This can also be done using a character set.
SELECT * from Fzq where regexp_like (value, ' 1[[:d igit:]]{4}60 ');
--Query value is not a pure number of records select * from Fzq where is not regexp_like (value, ' ^[[:d igit:]]+$ ');
--queries for records that do not contain any numbers in value.
SELECT * from Fzq where regexp_like (value, ' ^[^[:d igit:]]+$ ');
--query for records beginning with 12 or 1b. Case insensitive.
SELECT * from Fzq where regexp_like (value, ' ^1[2b] ', ' I ');
--query for records beginning with 12 or 1b. Case-sensitive.
SELECT * from Fzq where regexp_like (value, ' ^1[2b] ');
--The query contains blank records in the data.
SELECT * from Fzq where regexp_like (value, ' [[: Space:]] ');
--query all records that contain lowercase letters or numbers.
SELECT * from Fzq where regexp_like (value, ' ^ ([a-z]+|[ 0-9]+) ($ ');
--Query any record that contains punctuation.
SELECT * from Fzq where regexp_like (value, ' [[:p UNCT:]] ');

Example: Determine if a name is empty, less than two characters, and contains numbers and letters

Create or replace
FUNCTION CheckName (NameStr in VARCHAR2) RETURN integer
As
BEGIN
--compliant return 1, non-compliant return 0
if (NAMESTR is null or length (NAMESTR) <2) Then
return 0;
Else
if (NameStr like '% not named% ') then
RETURN 0;
End If;
If Regexp_like (NameStr, ' ^ ([a-z]+|[ 0-9]+| [a-z]+) $ ') then
return 0;
End If;
return 1;
End If;
END CheckName;

Source: http://www.cnblogs.com/einyboy/archive/2012/08/01/2617606.html

ORACLE RegExp Application Example