Oracle Study-- Oracle regular expression
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
