PostgreSQL 正则表达式 - 排除带有数字的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22436618/
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 Regular Expression - Excluding strings with numbers
提问by Trocader
I am starting with Postgre Regular Expressions, working on PostgreSQL 8.3 version.
我从 Postgre 正则表达式开始，在 PostgreSQL 8.3 版本上工作。
I have a table of records as following:
我有一个记录表如下：
record
-----------
a
ab
abc
123abc
132abc
123ABC
abc123
4567
5678
6789
I was going through a simple tutorial: www.oreillynet.com. Everything seemed clear to me until I came to the following query:
我正在学习一个简单的教程： www.oreillynet.com。在我遇到以下查询之前，一切似乎都很清楚：
SELECT record FROM myrecords WHERE record ~ '[^0-9]';
The tutorail says:
教程说：
this expression returns strings that don't include digits.
此表达式返回不包含数字的字符串。
But it returned following output to me:
但它向我返回了以下输出：
output
------
a
ab
abc
123abc
132abc
123ABC
abc123
Can anyone explain me this behaviour, please? I am then struggling with another expression on that site trying to exclude strings that include digits AND lower-case letters in the same string.
任何人都可以解释我这种行为吗？然后我在该站点上的另一个表达式中挣扎，试图排除在同一字符串中包含数字和小写字母的字符串。
回答by Ashalynd
This command:
这个命令：
SELECT record FROM myrecords WHERE record ~ '[^0-9]';
means that in the recordfield there should be at least one non-digit character (this is the meaning of the regex).
意味着在该record字段中应该至少有一个非数字字符（这是正则表达式的含义）。
If one looks for the records which would include digits and lower-case letter, then I would expect a regex like:
如果寻找包含数字和小写字母的记录，那么我希望有一个像这样的正则表达式：
SELECT record FROM myrecords WHERE record ~ '[0-9a-z]';
which would return all the records having at least one character which is a digit or lowercase letter.
这将返回至少具有一个数字或小写字母字符的所有记录。
If you want to get the records which have no digits, then you would have to use the following regex:
如果要获取没有数字的记录，则必须使用以下正则表达式：
SELECT record FROM myrecords WHERE record ~ '^[^0-9]+$';
Here, the ^character outside of square brackets means the beginning of the field, the $character means the end of the field, and we require that all characters in between are non-digits. +indicates that there should be at least one such characters. If we would also allow empty strings, then the regex would look like ^[^0-9]*$.
这里，^方括号外的$字符表示字段的开始，字符表示字段的结束，我们要求中间的所有字符都是非数字。+表示应该至少有一个这样的字符。如果我们也允许空字符串，那么正则表达式看起来像^[^0-9]*$.
回答by user2478690
Another simple solution:
另一个简单的解决方案：
SELECT record FROM myrecords WHERE record !~ '[0-9]';
回答by Cosmin Gruian
select 'a2' ~ '^[a-z]+$'; --false
select 'a' ~ '^[a-z]+$'; --true
'^[a-z]+$'=> checks only letters from a-z from the beginning(^) till the end of the string (+$)
'^[a-z]+$'=> 只检查 az 从开头 (^) 到字符串结尾 (+$) 的字母
If you want to check for numbers: '^[0-9]+$'
如果要检查数字： '^[0-9]+$'
If you want to check for numbers and a character, lets say "." :'^[0-9\.]+$', where "\" is an escape character
如果你想检查数字和字符，让我们说“。” : '^[0-9\.]+$'，其中“\”是转义字符
