3

I've searched everywhere for a decent and logical CHECK constraint to validate that an email is in the right format. So far I've found really long and unnecessary expressions like: 

create table t (
email varchar2(320) check (
regexp_like(email, '[[:alnum:]]+@[[:alnum:]]+\.[[:alnum:]]')
 )
);

and 

create table stk_t (
email varchar2(320) check (
email LIKE '%@%.%' AND email NOT LIKE '@%' AND email NOT LIKE '%@%@%'
 )
);

Surely there is a simpler way? I'm using Oracle 11g database and SQL Developer IDE. This is what I have: 

constraint Emails_Check check (Emails LIKE '%_@%_._%')

Can someone please let me know if this is the most efficient way of validating emails?

Ryan
  • 393
  • 7
  • 22
  • 5
    `really long and unnecessary expressions` most through email regexes are _much_ longer and more complicated than this. Have a look [here](http://stackoverflow.com/questions/201323/using-a-regular-expression-to-validate-an-email-address) to get started thinking about this. – Tim Biegeleisen May 11 '17 at 04:39
  • I don't see anything "unnecessary" in these expressions. Quite the opposite actually: the local part must not contain any '@'; a top level domain must consist of at least two letters; etc. You will end up with a more complex regexp_like. Maybe you'd prefer a trigger instead of a constraint, so you can use PL/SQL to check the string and raise an exception when it doesn't confirm to the rules for an email address. – Thorsten Kettner May 11 '17 at 06:07
  • Your check constraint considers '@@...' a valid email address which it obviously isn't. – Thorsten Kettner May 11 '17 at 06:10
  • There is no such thing as a "most efficient way" for validating emails - but some are better than others, some give too many false positives and others give too many false negatives. – Jeffrey Kemp May 11 '17 at 06:23
  • 1
    @ThorstenKettner, TLDs *can* have single letters, now :) http://stackoverflow.com/questions/7411255/is-it-possible-to-have-one-single-character-top-level-domain-name – Jeffrey Kemp May 11 '17 at 06:26
  • @Jeffrey Kemp: Thanks for clarifying. So a@b.c would be invalid now, as c is currently no valid TLD, but may become valid in the future. – Thorsten Kettner May 11 '17 at 06:52
  • `[:alnum:]` means `[a-zA-Z0-9]`, however characters like `.-_` are also permitted - and fairly common. – Wernfried Domscheit May 11 '17 at 06:53
  • Practically, of course, it's unlikely to be an issue in most cases. After all, any simple regex for email validation will have flaws no matter what. – Jeffrey Kemp May 11 '17 at 11:16

3 Answers3

2

You can try this

email varchar2(255) check (
email LIKE '%@%.%' AND email NOT LIKE '@%' AND email NOT LIKE '%@%@%'   )
C8H10N4O2
  • 18,312
  • 8
  • 98
  • 134
Ahmad Habib
  • 33
  • 1
  • 9
  • 1
    Welcome to Stack Overflow! Before answering a question, always read the existing answers. This answer has already been provided. In fact, the OP looks for a more simple solution. Some guidelines for writing good answers can be found [here](https://stackoverflow.com/help/how-to-answer). – dferenc Jan 01 '18 at 20:06
0
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}$'))
)

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
Dr. Parag C Shukla
  • 578
  • 5
  • 15
0

Stumbled upon this answer while hunting for a simple solution on the internet:

ALTER TABLE YourTableName
ADD CONSTRAINT YourConstraintName CHECK(YourColumnName LIKE '%___@___%.__%')

All points to @bhanu_nz here

Yashash Gaurav
  • 581
  • 5
  • 9