0

My purpose is to know how to give a valid pattern for a column which contains an email for each row, and also a column which contains a phone number on each row. I tried some patterns I tried online but they don't work. So I'm asking anybody who knows how to define these patterns to help me please.

Here's how I want the patterns:

In the email : the standard pattern that everybody uses. The phone number : The phone number must have 10 digits at max, starts with 0 and the next 9 digits can be any number from 0-9 Here is the code for table creation:

DROP TABLE COMITE_I;
create table COMITE_I
(
IDCOMITE_I             INTEGER              not null,
NOMCOMITE_I            VARCHAR2(100),
PRENOMCOMITE_I         VARCHAR2(100),
EMAILCOMITE_I          VARCHAR2(100),
MDPCOMITE_I            VARCHAR2(50),
NUMTELCOMITE_I         VARCHAR2(20),
constraint PK_COMITE_I primary key (IDCOMITE_I),
constraint CK_emailComite_I  CHECK(REGEXP_LIKE(emailComite_I,'[_A-Za-z0-9-\\+]+(\\.[_A-Za-z0-9-]+)*@[A-Za-z0-9-]+(\\.[A-Za-z0-9]+)*(\\.[A-Za-z]{2,})')),
constraint CK_numTelComite_I CHECK(REGEXP_LIKE(numTelComite_I,'0([0-9]{9})'))
);

And here's the SQL code for the insert :

INSERT INTO Comite_I (idComite_I,nomComite_I,prenomComite_I,emailComite_I,mdpComite_I,numTelComite_I) VALUES (1,'Nom1','Prenom1','truc@machin.com','azerty','0234567899');

Which returns the error

ERROR at line 1:
ORA-02290: check constraint (GESTION_CONGRES.CK_EMAILCOMITE_I) violated

Thanks in advance for your help, please keep in mind that I'm a beginner :) Thank you

Nagaraj S
  • 13,316
  • 6
  • 32
  • 53
user3078046
  • 31
  • 1
  • 7

1 Answers1

2

Firstly, it's impossible to validate an e-mail address using regular expressions as you've no idea whether it exists or not. It's just about possible tell whether it's potentially valid, but the Oracle implementation is not sophisticated enough to do so. This means that any e-mail "validation" you do in Oracle is subject to what we might call "business-rules", you define what you believe a valid e-mail address to be in the certain knowledge that you will be wrong on occasion.

You breach the constraint checking here because your regular expression is incorrect. If you change it to the following it will work:

regexp_like('truc@machin.com'
, '[_A-Za-z0-9-\+]+(\.[_A-Za-z0-9-]+)*@[A-Za-z0-9-]+(\.[A-Za-z0-9]+)*(\.[A-Za-z]{2,})')

SQL Fiddle

You were escaping dots too much...

I actually prefer to use the Oracle's POSIX implementation, I think it makes things clearer, though you would have to use the match parameter, i, which indicates that you want to do a case-insensitive search:

regexp_like('truc@machin.com'
, '[_-\+[:alnum:]]+(\.[_-[:alnum:]]+)*@[-[:alnum:]]+(\.[[:alnum:]]+)*(\.[[:alpha:]]{2,})'
, 'i')
Community
  • 1
  • 1
Ben
  • 51,770
  • 36
  • 127
  • 149
  • Thank you very much :) it worked perfectly (i used first one). – user3078046 Dec 24 '13 at 16:59
  • No problem @user3078046. If you feel like this answered your question consider [accepting it](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) - the green checkmark. It tells the community that your question is solved, but there's absolutely no obligation to do so. – Ben Dec 24 '13 at 17:30