REGEXP_LIKE, Regular Expressions

Reject Malformed Email Using Check Constraints with REGEXP_LIKE function

caret1

Using Check Constraints with Regular Expressions

was something I just wanted to experiment with. Turns out they can be effective. The example here will address a common problem when requesting information from a user. One can argue that the this type of check should be done at the application layer and not the database layer, but for demonstration purposes it’s in the database.  Let’s get to it shall we?

For this example I am using Database 12c:

Create a table to hold an ID, NAME, and EMAIL and insert a row.

CREATE TABLE EMAIL_ADDY
(
“ID”     NUMBER
“NAME”   VARCHAR2(20 BYTE)
“EMAIL”  VARCHAR2(50 BYTE)
);

email_table_insert

 

As you can see, ‘alton’ is not a legitimate email address, but without any validation checks, it is inserted into the database.

 

 

Now create the same table and add a Check Constraint, using a REGEXP Function, to the EMAIL column.

CREATE TABLE EMAIL_ADDY
(
“ID”     NUMBER
“NAME”   VARCHAR2(20 BYTE)
“EMAIL”  VARCHAR2(50 BYTE) CONSTRAINT email_format
CHECK (REGEXP_LIKE (email
, ‘^\w+(\.\w+)*+@\w+(\.\w+)+$’)
));

email_table_insert_cc

 

So far so good!  Our REGEXP_LIKE function allowed a legitimately formed email address to be inserted into the database.

 

 

 

Finally, let’s try that original insert:

email_table_insert_cc_test

There we have it. Next would be to trap this error and send something meaningful back to the user.

dbaOnTap

 

Related Posts Plugin for WordPress, Blogger...

1 Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.