Oracle 10g Regular Expression REGEXP_LIKE usage

Oracle 10g Regular Expression REGEXP_LIKE usage




There are four functions that support regular expressions in ORACLE:
1. REGEXP_LIKE: similar to LIKE
2. REGEXP_INSTR: similar to INSTR
3, REGEXP_SUBSTR: similar to SUBSTR
4. REGEXP_REPLACE: similar to REPLACE
They are used in the same way as Oracle SQL functions LIKE, INSTR, SUBSTR, and REPLACE,
However, they use POSIX Regular Expressions instead of the old percent sign (%) and wildcard character.

POSIX regular expressions are composed of standard metacharacters:
'^' Matches the start position of the input string. (Note that if ^ is used in the square brackets expression, this character set is not accepted, as shown in [^ [: digit:], not a number ).
'$' Matches the end position of the input string. If the Multiline attribute of the RegExp object is set, $ also matches '\ n' or' \ R '.
'.' Matches any single character except the line break.
'? 'Match the previous subexpression zero or once.
'+' Matches the previous subexpression once or multiple times.
'*' Matches the previous subexpression zero or multiple times.
'|' Indicates an option between the two items. Example '^ ([a-z] + | [0-9] +) $' indicates a string composed of all lowercase letters or numbers.
'()' Indicates the start and end positions of a subexpression.
'[]' Indicates a bracket expression.
'{M, n}' indicates the exact number of occurrences. m = <number of occurrences <= n, '{m}' indicates m occurrences, '{m ,} 'indicates at least
Appears m times.
\ Num matches num, where num is a positive integer. References to the obtained matching.
Character cluster:
[[: Alpha:] any letter.
[[: Digit:] any number.
[[: Alnum:] Any letter or number.
[[: Space:] any white characters.
[[: Upper:] Any uppercase letter.
[[: Lower:] Any lowercase letter.
[[: Punct:] Any punctuation marks.
[[: Xdigit:] Any hexadecimal number, which is equivalent to [0-9a-fA-F].
Operation priority of various operators
\ Escape Character
(),(? :),(? =), [] Parentheses and square brackets
*, + ,?, {N}, {n ,}, {n, m} qualifier
^, $, Anymetacharacter location and Sequence
|

Examples
The following query returns the first and last names for those employees with a first name of Steven or Stephen (wherefirst_name begins withSte and ends with en and in between is 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 for those employees with a double vowel in their last name (wherelast_name contains 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

Lab test:


1. Create a table
Create table gyj (id varchar (4), value varchar (10 ));


2. Data insertion
Insert into gyj values ('1', '20140901 ');
Insert into gyj values ('2', '20140901 ');
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', '00000045 ');
Insert into gyj values ('8', 'adc de ');
Insert into gyj values ('9', 'adc,. de ');
Insert into gyj values ('10', '1b ');
Insert into gyj values ('10', 'abcbvbnb ');
Insert into gyj values ('11', '123 ');
Insert into gyj values ('11', '123 ');
Commit;


3. regexp_like
-- Query records whose names start with 1 and end with 60 and whose length is 7 characters
Select * from gyj where value like '1 ____ 60 ';
Select * from gyj where regexp_like (value, '1... 60 ');


-- Query records whose names start with 60 and end with 1 and whose length is 7 digits and all are numbers.
-- Using like is not a good implementation.
Select * from gyj where regexp_like (value, '1 [0-9] {4} 60 ');


-- The character set can also be used.
Select * from gyj where regexp_like (value, '1 [[: digit:] {4} 60 ');


-- Query records whose values are not pure numbers
Select * from gyj where not regexp_like (value, '^ [: digit:] + $ ');


-- Query records whose values do not contain any numbers.
Select * from gyj where regexp_like (value, '^ [^ [: digit:] + $ ');

-- Query records starting with 12 or 1b. The records are case insensitive.
Select * from gyj where regexp_like (value, '^ 1 [2b]', 'I ');

-- Query records starting 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:]');

-- Query all records that contain lowercase letters or numbers.
Select * from gyj where regexp_like (value, '^ ([a-z] + | [0-9] +) $ ');

-- Query any records containing punctuation marks.
Select * from gyj where regexp_like (value, '[[: punct:]');

Note:
Regular Expressions only search, replace, format, and other functions. Regular Expressions are usually used for back-reference formatting. The length and concatenate are not calculated.

**************************************** ********************************
Enable/disable: It has constraints/no constraints on future data.

Validate/novalidate has constraints/no constraints on existing data.



It is a field constraint, which means you must create a constraint in the CUST_FIRST_NAME field of the CUSTOMERS table so that no number can be entered for this field.

Analog answer A, starting with A A-Z, can be followed by A number, this does not match the meaning of the question!
Gyj @ OCM> alter table gyj add constraint cust_f_name CHECK (REGEXP_LIKE (value, '^ date ter') NOVALIDATE;

Table altered.

Gyj @ OCM> insert into gyj values (105, 'a-Z12345 ');


1 row created.


Gyj @ OCM> insert into gyj values (105, '-AZ12345 ');
Insert into gyj values (105, '-AZ12345 ')
*
ERROR at line 1:
ORA-02290: check constraint (GYJ. CUST_F_NAME) violated




Gyj @ OCM> insert into gyj values (105, 'z-A12345 ');
Insert into gyj values (105, 'z-A12345 ')
*
ERROR at line 1:
ORA-02290: check constraint (GYJ. CUST_F_NAME) violated

Simulate Answer B: It starts with a number 0 or 9, so it does not match the meaning of the question!
Gyj @ OCM> alter table gyj drop CONSTRAINT cust_f_name;


Table altered.

Gyj @ OCM> alter table gyj add constraint cust_f_name CHECK (REGEXP_LIKE (value, '^ [09]') NOVALIDATE;


Table altered.

Gyj @ OCM> insert into gyj values (105, '09g ');


1 row created.


Gyj @ OCM>
Gyj @ OCM> insert into gyj values (105, '90g ');


1 row created.




Gyj @ OCM> insert into gyj values (105, 'memory G ');
Insert into gyj values (105, 'memory G ')
*
ERROR at line 1:
ORA-02290: check constraint (GYJ. CUST_F_NAME) violated

Simulation body answer C:
Gyj @ OCM> alter table gyj drop CONSTRAINT cust_f_name;


Table altered.


Gyj @ OCM> alter table gyj add constraint cust_f_name CHECK (REGEXP_LIKE (value, '[[: alpha:]') NOVALIDATE;


Table altered.

Gyj @ OCM> 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

Gyj @ OCM> insert into gyj values (105, 'gyj ');


1 row created.

Simulated answer D: [[: digit:] Any number does not match the question!
Gyj @ OCM> alter table gyj drop CONSTRAINT cust_f_name;


Table altered.


Gyj @ OCM> alter table gyj add constraint cust_f_name CHECK (REGEXP_LIKE (value, '[[: digit:]') NOVALIDATE;


Table altered.

Gyj @ OCM> insert into gyj values (105, '1 ');


1 row created.

Gyj @ OCM> insert into gyj values (105, 'gyj ');
Insert into gyj values (105, 'gyj ')
*
ERROR at line 1:
ORA-02290: check constraint (GYJ. CUST_F_NAME) violated