Shulou(Shulou.com)06/01 Report--

Oracle Study--Oracle Regular Expressions

Case requirements:

To create a check constraint , the required field input content must be "00[number][number][a-z]":

The following constraints are established by regular expressions:

17:20:13 SCOTT@ prod >desc card

Name Null? Type

----------------------------------------------------------------- -------- --------------------------------------------

ID NOT NULL VARCHAR2(10)

17:22:53 SCOTT@ prod >

17:19:05 SCOTT@ prod >alter table card drop constraint card_ck;

Table altered.

Create regular expressions:

17:19:19 SCOTT@ prod >alter table card add constraint card_ck check (REGEXP_LIKE(id,'00[[:digit:]]{2}[[:lower:]]{1}'));

Table altered.

Elapsed: 00:00:00.06

Testing:

17:19:33 SCOTT@ prod >insert into card values ('0055a');

1 row created.

Elapsed: 00:00:00.01

17:19:50 SCOTT@ prod >insert into card values ('0065z');

1 row created.

Elapsed: 00:00:00.00

17:20:04 SCOTT@ 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

Regular expression description:

Table 5: REGEXP_LIKE operator

syntax

description

REGEXP_LIKE(source_string, pattern

[, match_parameter])

source_string supports character data types (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB, but not LONG). The pattern parameter is another name for a regular expression. match_parameter allows optional parameters such as handling newline characters, preserving multiline formatting, and providing control over case sensitivity.

Table 1: Locating metacharacters

metacharacters

description

^

Position an expression to the beginning of a line

$

Position an expression to the end of a line

Table 2: Quantifiers or Repeat Operators

quantifier

description

*

Match 0 or more times

?

Match 0 or 1 times

+

Match 1 or more times

{m}

exactly m times.

{m,}

Match at least m times

{m, n}

Match at least m times but no more than n times

Table 3: Predefined POSIX character classes

character class

description

[:alpha:]

alphabetic characters

[:lower:]

lowercase alphabetic characters

[:upper:]

upper-case character

[:digit:]

digital

[:alnum:]

alphanumeric characters

[:space:]

Blank characters (prohibited from printing) such as carriage return, line feed, vertical tab, and form feed

[:punct:]

punctuation characters

[:cntrl:]

Control character (print prohibited)

[:print:]

printable characters

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.