用于验证电子邮件地址的 PostgreSQL 正则表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10164758/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL regex to validate email addresses
提问by Scott Marlowe
CREATE OR REPLACE FUNCTION addUploader(INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
DECLARE
u_id ALIAS FOR ;
username ALIAS FOR ;
email ALIAS FOR ;
BEGIN
IF email NOT LIKE '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$' THEN
RAISE EXCEPTION 'Wrong E-mail format %', email
USING HINT = 'Please check your E-mail format.';
END IF ;
INSERT INTO uploader VALUES(u_id,username,email);
IF NOT FOUND THEN
RETURN 'Error';
END IF;
RETURN 'Successfully added' ;
EXCEPTION WHEN unique_violation THEN
RAISE NOTICE 'This ID already exists. Specify another one.' ;
RETURN 'Error' ;
END ; $$ LANGUAGE 'plpgsql' ;
SELECT addUploader(25,'test','[email protected]');
This regex does not accept a correct email address. It should accept [email protected] is also rejecting any other string.
此正则表达式不接受正确的电子邮件地址。它应该接受 [email protected]。它还拒绝任何其他字符串。
stevengmailcom - rejected
stevengmailcom - 拒绝
Everything is being rejected.
一切都在被拒绝。
What am i doing wrong?
我究竟做错了什么？
回答by mu is too short
You don't use LIKE with regexes in PostgreSQL, you use the ~, ~*, !~, and !~*operators:
你不会在PostgreSQL的正则表达式与LIKE使用，使用~，~*，!~，和!~*运营商：
~
Matches regular expression, case sensitive
~*
Matches regular expression, case insensitive
!~
Does not match regular expression, case sensitive
!~*
Does not match regular expression, case insensitive
~
匹配正则表达式，区分大小写
~*
匹配正则表达式，不区分大小写
!~
不匹配正则表达式，区分大小写
!~*
不匹配正则表达式，不区分大小写
So your test should look more like this:
所以你的测试应该更像这样：
IF email !~ '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$' THEN
You also might want to hunt down a better regex for email addresses, "[email protected]" is a valid email address but your regex doesn't like it.
您可能还想为电子邮件地址寻找更好的正则表达式，“[email protected]”是一个有效的电子邮件地址，但您的正则表达式不喜欢它。
回答by Scott Marlowe
Validating email addresses is fraught with peril. It's far easier to create a regex that refuses to accept valid email addresses than one that rejects invalid ones only. Here's the perl regexthat's RFC822 compliant:
验证电子邮件地址充满危险。创建拒绝接受有效电子邮件地址的正则表达式比仅拒绝无效电子邮件地址的正则表达式要容易得多。这是符合 RFC822的perl 正则表达式：
My advice is to accept anything, then use email to verify it works. If you MUST do validation then use the perl module listed on that page and pl/perl.
我的建议是接受任何东西，然后使用电子邮件来验证它是否有效。如果您必须进行验证，请使用该页面上列出的 perl 模块和 pl/perl。
回答by Tim Pote
I think you want one of the tilde operatorsinstead of like.
我认为你想要一个波浪号运算符而不是like.
likehas a different syntax (for instance it uses %instead of .*) consisting only of wildcards, and they must match the entire string, so anchors like ^and $can't be used with like. I personally think of it more like file globbing than actual pattern matching.
like具有仅由通配符组成的不同语法（例如，它使用%代替.*），并且它们必须匹配整个字符串，因此锚点 like^和$不能与like. 我个人认为它更像是文件通配而不是实际的模式匹配。
Tilde operators give you a robust regex syntax more akin to egrep, sed, and awk.
波浪号运算符为您提供了更类似于 egrep、sed 和 awk 的强大正则表达式语法。
