如何编写查询以确保电子邮件包含@ [英] How to write a query to ensure email contains @

问题描述

我在db2中创建一个数据库，我想添加一个约束来验证用户是否插入有效的电子邮件地址，其中包含%@%.%。没有运气...任何建议？

解决方案

您可以使用LIKE与通配符。请参阅这里在DB2上的通配符。

下划线字符（_）表示任何单个字符。 >

百分号字符（％）表示一个字符串，为零个或多个字符。

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

这将忽略以下情况（有效电子邮件的简单版本）：

• 之前至少有两个字符的@;


• 电子邮件中至少有一个字符的电子邮件至少两个字符之间。结束。

您可以在 sqlfiddle 。

要将其作为约束添加，您可以（正如mustaccio在评论中所说）：

  alter table your_table add constraint chk_email check（email like'%_@__%.__%'）
  

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?

解决方案

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

这篇关于如何编写查询以确保电子邮件包含@的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！