How to write a query to ensure email contains @

Mindan picture Mindan · Oct 3, 2013 · Viewed 27.1k times · Source

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?

Answer

Filipe Silva picture Filipe Silva · Oct 3, 2013

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 '%_@__%.__%')