8

I'm creating a database in db2 and I want to add a constrain to validate whether the user inserting a valid email address, that contain %@%.% . Without luck...any advice?

Mindan
  • 837
  • 4
  • 14
  • 30

3 Answers3

16

You can use LIKE with wildcards. See here for the wildcards on DB2.

The underscore character ( _ ) represents any single character.

The percent sign character (%) represents a string of zero or more characters.

SELECT email 
FROM YourTable 
WHERE email NOT LIKE '%_@__%.__%'

This will ignore the following cases (simple version for valid emails):

  • emails that have at least one character before the @;
  • emails that have at least two characters between @ and .;
  • emails that have at least two characters between . and the end.

You can see an example in MySql in sqlfiddle.

To add it as a constraint, you do (as mustaccio said in a comment):

alter table your_table add constraint chk_email check (email like '%_@__%.__%')
Community
  • 1
  • 1
Filipe Silva
  • 20,688
  • 4
  • 50
  • 67
  • 1
    This works with DB2, I can confirm. To add a check constraint to an existing column, use `alter table your_table add constraint chk_email check (email like '%_@__%.__%')` – mustaccio Oct 03 '13 at 14:43
  • There is no problem at all with your approach, I just wanted to indicate that I've tried both ways any they worked. another thing I thought actually it is possible to accept two answers. – Mindan Oct 04 '13 at 10:37
0

You could create a trigger that checks the given string with a regular expression that describes the structure of an email

^[A-Za-z0-9]+@[A-Za-z0-9]+.[A-Za-z0-9]+$

However, the regular expression of an email is not easy to define: Using a regular expression to validate an email address

DECLARE RET VARCHAR(32);
SET RET = XMLCAST (
    XMLQUERY ('fn:matches($TEXT,"^[A-Za-z0-9]+@[A-Za-z0-9]+.[A-Za-z0-9]+$")' 
    PASSING TEXT AS "TEXT"
    ) AS VARCHAR(32));

Once you have the value of RET (true or false) you can do something in the trigger.

You can test the regular expression you want to use from command line:

db2 "xquery fn:matches(\"johndoe@mail.com\",\"^[A-Za-z0-9]+@[A-Za-z0-9]+.[A-Za-z0-9]+$\")"
Community
  • 1
  • 1
AngocA
  • 7,545
  • 5
  • 38
  • 52
0

Ensuring just @ wouldn't be an ideal check for validating email address.

Validation can be something like

SELECT Email FROM YourTable WHERE email NOT REGEXP_LIKE(Email, '^[a-zA-Z0-9][a-zA-Z0-9._-]*@[a-zA-Z0-9][a-zA-Z0-9._-]*\\.[a-zA-Z]{2,4}$')

or you can even do a simpler validation to reduce false negatives and run faster

SELECT Email FROM YourTable WHERE Email NOT LIKE '%_@__%.__%'
Naren Babu R
  • 396
  • 8
  • 32