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