Oracle 11g - Check constraint with RegEx
A check constraint follows the same syntax rules as conditions for a WHERE clause:
alter table foo
add constraint check_email
check (REGEXP_LIKE(email,'your_regex_goes_here','I'));
More details in the manual:
- for Oracle 11 - http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF52141
- for Oracle 12 - https://docs.oracle.com/database/121/SQLRF/conditions007.htm#SQLRF52141
Edit:
There are however some restrictions on what you can actually use in a check constraint:
- Oracle 11 - http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52205
- Oracle 12 - https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52205
CREATE TABLE MYTABLE(
EMAIL VARCHAR2(30) CHECK(REGEXP_LIKE (EMAIL,'^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$'))
);
ALTER TABLE MYTABLE ADD(CONTACT NUMBER(10) CHECK(REGEXP_LIKE(CONTACT,'[0-9]{10}')));
Explanation of Regular Expression
^ #start of the line
[_A-Za-z0-9-]+ # must start with string in the bracket [ ], must contains one or more (+)
( # start of group #1
\\.[_A-Za-z0-9-]+ # follow by a dot "." and string in the bracket [ ], must contains one or more (+)
)* # end of group #1, this group is optional (*)
@ # must contains a "@" symbol
[A-Za-z0-9]+ # follow by string in the bracket [ ], must contains one or more (+)
( # start of group #2 - first level TLD checking
\\.[A-Za-z0-9]+ # follow by a dot "." and string in the bracket [ ], must contains one or more (+)
)* # end of group #2, this group is optional (*)
( # start of group #3 - second level TLD checking
\\.[A-Za-z]{2,} # follow by a dot "." and string in the bracket [ ], with minimum length of 2
) # end of group #3
$ #end of the line