Oracle 10g Regular Expression Regexp_like usage detailed

There are four main functions in Oracle that support regular expressions:
1,regexp_like: Similar to like function
2,REGEXP_INSTR: Similar to INSTR function
3,REGEXP_SUBSTR: Similar to SUBSTR function
4,regexp_replace: Similar to REPLACE function

They are used in the same usage as Oracle SQL functions like, INSTR, SUBSTR, and replace.

However, they use POSIX regular expressions instead of the old percent semicolon (%) and wildcard (_) characters.

POSIX Regular expressions are composed of standard metacharacters (metacharacters):
' ^ ' matches the start position of the input string (note that if you use ^ in a bracket expression, it means that the character set is not accepted, such as [^[:d igit:], not a number).
' $ ' 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 a newline character.
'? ' 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,} ' means at least
appear m times.
\num matches num, where num is a positive integer. A reference to the match that was obtained.
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.
[[:p 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
|

Examples

The following query returns the "I" and "names for those employees" with a "a", "Steven" or "Stephen" (wherefirs T_name begins withste and ends with en and in between are Eitherv Orph):

SELECT first_name, last_name from
employees
WHERE regexp_like (first_name, ' ^ste (v|ph) en$ ');

First_Name           last_name
---------------------------------------------
Steven               King
Steven               Markle
Stephen              Stiles

The following query returns the last name of those employees with a double vowel on their last name (Wherelast_name conta INS two adjacent occurrences of eithera,e, I,o, or u, regardless of case):

SELECT last_name
from Employees
WHERE regexp_like (last_name, ' ([aeiou]) \1 ', ' I ');

Last_Name
-------------------------
De Haan
Greenberg
Khoo
Gee
Greene
Lee
Bloom
Feeney

Experimental test:
1. Create a table
CREATE TABLE Gyj (ID varchar (4), Value varchar (10));
2. Data insertion
INSERT into GYJ values (' 1 ', ' 1234560 ');
INSERT into GYJ values (' 2 ', ' 1234560 ');
INSERT into Gyj values (' 3 ', ' 1b3b560 ');
INSERT into GYJ values (' 4 ', ' abc ');
INSERT into GYJ values (' 5 ', ' ABCDE ');
INSERT into GYJ values (' 6 ', ' adreasx ');
INSERT into GYJ values (' 7 ', ' 123 45 ');
INSERT into GYJ values (' 8 ', ' ADC de ');
INSERT into GYJ values (' 9 ', ' adc,.de ');
INSERT into gyj values (' Ten ', ' 1 B ');
INSERT into gyj values (' Ten ', ' Abcbvbnb ');
INSERT into GYJ values (' 11 ', ' 11114560 ');
INSERT into GYJ values (' 11 ', ' 11124560 ');
Commit

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

3.regexp_like


--The record that ends with 1 at the start of 60 in query value and is 7 bits long


SELECT * from Gyj where value like ' 1____60 ';


SELECT * from Gyj where regexp_like (value, ' 1....60 ');


--query value with a record ending with 1 at the beginning of 60 and a length of 7 bits and all numbers.


-using like is not a good implementation.


SELECT * from Gyj where regexp_like (value, ' 1[0-9]{4}60 ');


-can also be implemented with character sets.


SELECT * from Gyj where regexp_like (value, ' 1[[:d igit:]]{4}60 ');


--Query value is not a pure numeric record


SELECT * from Gyj where not regexp_like (value, ' ^[[:d igit:]]+$ ');


--The query value does not contain any number of records.


SELECT * from Gyj where regexp_like (value, ' ^[^[:d igit:]]+$ ');


-Queries a record that starts with 12 or 1b. is case-insensitive.


SELECT * from Gyj where regexp_like (value, ' ^1[2b] ', ' I ');


-Queries a record that starts with 12 or 1b. Case sensitive.


SELECT * from Gyj where regexp_like (value, ' ^1[2b] ');


--The query data contains blank records.


SELECT * from Gyj where regexp_like (value, ' [[: Space:]] ');


--queries all records that contain lowercase letters or numbers.


SELECT * from Gyj where regexp_like (value, ' ^ ') ([a-z]+|[ 0-9]+) $ ');


--queries any records that contain punctuation marks.


SELECT * from Gyj where regexp_like (value, ' [[:p UNCT:]] ');


Note:


Regular expressions are just search, replace, format, and so on, format generally with a back reference, no calculation length and concatenate (connection series) of the


************************************************************************


enable/disable is bound/unconstrained for future data.


validate/novalidate has constraints/No constraints on existing data.