How to write a request to ensure email contains @

Question

I am creating a database in db2 and I want to add a restriction to check if the user enters the correct email address that contains %@%.%. No luck ... any advice?

Answer 1

You can use LIKE with wildcards. See here for wildcards in DB2.

The underscore (_) represents any single character.

The percent sign (%) 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 @;
• which have at least two characters between @ and .;
• which have at least two characters. and the end.

You can see an example in MySql in sqlfiddle .

To add it as a restriction, you do (as pointed out in the mustaccio comments):

 alter table your_table add constraint chk_email check (email like '% _@ __%.__%') 

Answer 2

You can create a trigger that validates a given string with a regular expression that describes the email structure

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

However, email regular expression is not easy to determine: Using regular expression to validate email addresses

 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 a RET value (true or false), you can do something in the trigger.

You can check the regex that you want to use from the command line:

 db2 "xquery fn:matches(\" [e-mail] \",\"^[A-Za-z0-9] +@ [A-Za-z0-9]+.[A-Za-z0-9]+$\")"