Oracle 10g Regular Expression Regexp_like usage

Occasionally need to understand, study this article, reprint record

From: http://www.2cto.com/database/201304/206573.html

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 (note that if you use ^ in a square 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 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]+) $ ' represents 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

|

Examples

The following query returns the first and last names for those employees with a first name of 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 is those employees with a double vowel in their last name (Wherelast_name conta Ins 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

3.regexp_like

--query value with a record ending at 1 in 60 and a length of 7 bits

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

SELECT * from Gyj 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 Gyj where regexp_like (value, ' 1[0-9]{4}60 ');

--This can also be done using a character set.

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

--query for records that are not pure numbers in value

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

--queries for records that do not contain any numbers in value.

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

--query for records beginning with 12 or 1b. Case insensitive.

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

--query for records beginning with 12 or 1b. Case-sensitive.

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

--The query contains blank records in the data.

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

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

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

--Query any record that contains punctuation.

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

Attention:

Regular expressions are just search, replace, format, and so on, formatting is generally used back to reference, does not calculate length and concatenate (connected concatenation)

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

Enable/disable is bound/unconstrained for future data.

Validate/novalidate is bound/unconstrained to existing data.

is a field constraint, meaning that you want to create a constraint on the field cust_first_name of the table customers so that the field cannot enter a number.

Analog answer A, beginning with a-Z, can be used in the following numbers, so it does not meet test instructions!

[Email protected]> ALTER TABLE gyj ADD CONSTRAINT cust_f_name CHECK (regexp_like (value, ' ^a-z ')) novalidate;

Table altered.

[Email protected]> INSERT INTO gyj values (' a-z12345 ');

1 row created.

[Email protected]> INSERT INTO gyj values ('-az12345 ');

INSERT into GYJ values ("-az12345")

*

ERROR at line 1:

Ora-02290:check constraint (GYJ. Cust_f_name) violated

[Email protected]> INSERT INTO gyj values (' z-a12345 ');

INSERT into GYJ values ("z-a12345")

*

ERROR at line 1:

Ora-02290:check constraint (GYJ. Cust_f_name) violated

Analog Answer B: Start with a 0 or 9 number, so it doesn't fit test instructions!

[Email protected]> ALTER TABLE gyj drop CONSTRAINT cust_f_name;

Table altered.

[Email protected]> ALTER TABLE gyj ADD CONSTRAINT cust_f_name CHECK (regexp_like (value, ' ^[09] ')) novalidate;

Table altered.

[Email protected]> INSERT INTO gyj values (09g ');

1 row created.

[Email protected]>

[Email protected]> INSERT INTO gyj values (90g ');

1 row created.

[Email protected]> [email protected]> insert INTO gyj values (190g ');

INSERT into GYJ values (190g ')

*

ERROR at line 1:

Ora-02290:check constraint (GYJ. Cust_f_name) violated

Analog Body Answer c:

[Email protected]> ALTER TABLE gyj drop CONSTRAINT cust_f_name;

Table altered.

[Email protected]> ALTER TABLE gyj ADD CONSTRAINT cust_f_name CHECK (regexp_like (value, ' [[: Alpha:]] ')) novalidate;

Table altered.

[Email protected]> INSERT INTO GYJ values (105, ' 1 ');

INSERT into GYJ values (105, ' 1 ')

*

ERROR at line 1:

Ora-02290:check constraint (GYJ. Cust_f_name) violated

[Email protected]> INSERT INTO gyj values (' Gyj ');

1 row created.

Analog answer d:[[:D igit:]] Any number, does not conform to test instructions!

[Email protected]> ALTER TABLE gyj drop CONSTRAINT cust_f_name;

Table altered.

[Email protected]> ALTER TABLE gyj ADD CONSTRAINT cust_f_name CHECK (regexp_like (value, ' [[:d Igit:]])) novalidate;

Table altered.

[Email protected]> INSERT INTO GYJ values (105, ' 1 ');

1 row created.

[Email protected]> INSERT INTO gyj values (' Gyj ');

INSERT into GYJ values ("Gyj")

*

ERROR at line 1:

Ora-02290:check constraint (GYJ. Cust_f_name) violated

Oracle 10g Regular Expression Regexp_like usage