postgresql Postgres 查询以检查字符串是否为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19975257/
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
Postgres query to check a string is a number
提问by user2990782
Can anyone tell me the query to check whether a string is a number(double precision). It should return true if the string is number. else it should return false.
谁能告诉我检查字符串是否为数字（双精度）的查询。如果字符串是数字，它应该返回 true。否则它应该返回false。
consider :
考虑 ：
s1 character varying;
s2 character varying;
s1 ='12.41212' => should return true
s2 = 'Service' => should return false
回答by a_horse_with_no_name
I think the easiest way would be a regular expression match:
我认为最简单的方法是正则表达式匹配：
select '12.41212' ~ '^[0-9\.]+$'
=> true
select 'Service' ~ '^[0-9\.]+$'
=> false
回答by ns16
I fixed the regular expression that a_horse_with_no_name has suggested.
我修复了 a_horse_with_no_name 建议的正则表达式。
SELECT '12.41212' ~ '^\d+(.\d+)?$'; #true
SELECT 'Service' ~ '^\d+(.\d+)?$'; #false
回答by paracosmo
I would like to propose another suggestion, since 12a345returns trueby ns16's answer.
我想提出另一个建议，因为通过 ns16 的回答12a345返回true。
SELECT '12.4121' ~ '^\d+(\.\d+)?$'; #true
SELECT 'ServiceS' ~ '^\d+(\.\d+)?$'; #false
SELECT '12a41212' ~ '^\d+(\.\d+)?$'; #false
SELECT '12.4121.' ~ '^\d+(\.\d+)?$'; #false
SELECT '.12.412.' ~ '^\d+(\.\d+)?$'; #false
回答by detzu
If you want to check with exponential , +/- . then the best expression is :
如果你想检查指数，+/-。那么最好的表达是：
^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$
resulting in:
导致：
select '12.41212e-5' ~ '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$' ;
as true.
一样真实。
The expression is from: https://www.regular-expressions.info/floatingpoint.html
表达式来自：https: //www.regular-expressions.info/floatingpoint.html
You can check for other types of numbers, for example if you expect decimal, with a sign.
您可以检查其他类型的数字，例如，如果您希望使用带符号的十进制数。
select '-12.1254' ~ '^[-+]?[0-9]*\.?[0-9]+$';
回答by jhoanna
select s1 ~ '^\d+$';
select s2 ~ '^\d+$';
