ORACLE – How to check the value inserted in the column does not have numbers?

Technologies: Oracle (10g and higher versions)

This can be easily achieved by using the Regular Expression Support in ORACLE. The following example shows how to check the value inserted in the column does not have numbers.

Example:

CREATE TABLE test_constraints (

colA VARCHAR2(2)
);

Table created

ALTER TABLE test_constraints ADD CONSTRAINTS check_colA
CHECK (REGEXP_INSTR(colA,'[[:digit:]]')=0) NOVALIDATE;

Table altered

INSERT INTO test_constraints

VALUES ('12');

ORA-02290: check constraint (CHECK_COLA) violated

INSERT INTO test_constraints
VALUES ('1a');

ORA-02290: check constraint (CHECK_COLA) violated

INSERT INTO test_constraints
VALUES ('a1');

ORA-02290: check constraint (CHECK_COLA) violated

INSERT INTO test_constraints
VALUES ('aa');

1 row inserted

INSERT INTO test_constraints

VALUES ('a');

1 row inserted

In the above example I used the REGEXP_INSTR function to find the numbers in the inserted value. If the inserted value contains numbers it violates the Check_ColA constraint and ORACLE will throw the error message.