Oracle Study---oracle Regular expression

Oracle Study---oracle Regular expression

Case Requirements:

To set up a check constraint, require that the field input must be "00[number][number][a-z":

The following constraints (constraint) are established through regular expressions:

17:20:13 [email protected] PROD >desc card
Name Null? Type
----------------------------------------------------------------- -------- -------------------------------------- ------
ID not NULL VARCHAR2 (10)

17:22:53 [email protected] PROD >

17:19:05 [email protected] PROD >alter table card drop constraint card_ck;

Table altered.

To establish a regular expression:
17:19:19 [email protected] PROD >alter table card add constraint card_ck check (regexp_like (ID, ' 00[[:d igit:]]{2}[[:low Er:]]{1} ');

Table altered.

elapsed:00:00:00.06

Test:

17:19:33 [email protected] PROD >insert into card values (' 0055a ');

1 row created.

elapsed:00:00:00.01
17:19:50 [email protected] PROD >insert into card values (' 0065z ');

1 row created.

elapsed:00:00:00.00

17:20:04 [email protected] PROD >insert into card values (' 1188a ');
Insert into card values (' 1188a ')
*
ERROR at line 1:
Ora-02290:check constraint (SCOTT. CARD_CK) violated

elapsed:00:00:00.01

The Regular expression Description:

Table 5:regexp_like operator

Grammar	Description
Regexp_like (source_string, pattern [, Match_parameter])	source_string supports character data types (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 , and NCLOB, but does not include LONG ). The pattern parameter is another name for the regular expression. Match_parameter allows optional parameters such as handling line breaks, preserving multiline formatting, and providing case-sensitive control.

Table 1: Positional metacharacters

Metacharacters	Description
^	To position an expression at the beginning of a line
$	To position an expression at the end of a line

Table 2: quantifiers or repeating operators

Quantifiers	Description
*	Match 0 or more times
?	Match 0 or 1 times
+	Match 1 or more times
{m}	match exactly m Times
match at least  m  times
{m, n}	match at least m times but not more than N Times

Table 3: Pre-defined POSIX character classes

Character class	Description
[: Alpha:]	Alphabetic characters
[: Lower:]	Lowercase alphabetic characters
[: Upper:]	Uppercase characters
[:d Igit:]	Digital
[: Alnum:]	Alpha-Numeric characters
[: Space:]	White space characters (prohibit printing), such as carriage returns, line feeds, vertical tabs, and page breaks
[:p UNCT:]	Punctuation character
[: Cntrl:]	control characters (prohibit printing)
[:p rint:]	printable characters

This article is from the "Tianya blog," Please make sure to keep this source http://tiany.blog.51cto.com/513694/1657537

Oracle Study---oracle Regular expression