REGEXP_LIKE( email not working in Oracle PL SQL

Question

The regular expression I have created in the design of my database for verifying e-mail addresses is not working, even though I've read multiple Oracle forums which use the exact same expression. Every time I try to insert an email I get a check constraint violation.

CONSTRAINT check_email CHECK(REGEXP_LIKE(
Email,'^[A-Za-z0-9._-]+@[A-Za-z0-9._-]+\.[A-Za-z]{2-4}$'))

when trying INSERTS of the form:

INSERT INTO Member VALUES(0042, 'jasper@example.ie'); 

Can anyone shed any light on this?

Thanks!

Answer 1

I tried to replicate your issue. The problem is use of '-' in your range for last alphabets after the period. Use a comma instead.

Consider:

Table:

    CREATE TABLE abc_1(abc_id NUMBER(10), email VARCHAR2(100));

Constraint(Same as yours):

    ALTER TABLE abc_1 ADD CONSTRAINT abc_email_check CHECK(REGEXP_LIKE(Email,'^[A-Za-z0-9._-]+@[A-Za-z0-9._-]+\.[A-Za-z]{2-4}$'));

Insert Statement:

    INSERT INTO abc_1 VALUES (1001, 'myEmail@service.c'); --Fails, expected
    INSERT INTO abc_1 VALUES (1001, 'myEmail@service.com'); --Fails, not expected

The problem is with the range specification {2-4}. Which should rather be {2,4} Replace your check constraint as:

    ALTER TABLE abc_1 ADD CONSTRAINT abc_email_check CHECK(REGEXP_LIKE(Email,'^[A-Za-z0-9._-]+@[A-Za-z0-9._-]+\.[A-Za-z]{2,4}$'));

    INSERT INTO abc_1 VALUES (1001, 'myEmail@service.c'); --Fails, expected
    INSERT INTO abc_1 VALUES (1001, 'myEmail@service.com'); --Success, expected

When specifying range in regular expression, the way is {m,n}, where m is lower limit and n being the upper limit.