为了账号安全,请及时绑定邮箱和手机立即绑定

PostgreSQL的isnumeric()

/ 猿问

PostgreSQL的isnumeric()

蓝山帝景 2019-11-03 04:00:23

我需要确定给定的字符串是否可以在SQL语句中解释为数字(整数或浮点数)。如下所示:


SELECT AVG(CASE WHEN x ~ '^[0-9]*.?[0-9]*$' THEN x::float ELSE NULL END) FROM test

我发现Postgres的模式匹配可用于此目的。所以我适应中给出的声明这个地方纳入浮点数。这是我的代码:


WITH test(x) AS (

    VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),

    ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'))


SELECT x

     , x ~ '^[0-9]*.?[0-9]*$' AS isnumeric

FROM test;

输出:


    x    | isnumeric 

---------+-----------

         | t

 .       | t

 .0      | t

 0.      | t

 0       | t

 1       | t

 123     | t

 123.456 | t

 abc     | f

 1..2    | f

 1.2.3.4 | f

(11 rows)

如您所见,前两个项目(空字符串''和唯一句点'.')被错误分类为数字类型(不是)。目前,我无法对此进一步了解。任何帮助表示赞赏!


更新基于这个答案(和评论),我适应的模式:


WITH test(x) AS (

    VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),

    ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'), ('1x234'), ('1.234e-5'))


SELECT x

     , x ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$' AS isnumeric

FROM test;

这使:


     x    | isnumeric 

----------+-----------

          | f

 .        | f

 .0       | t

 0.       | t

 0        | t

 1        | t

 123      | t

 123.456  | t

 abc      | f

 1..2     | f

 1.2.3.4  | f

 1x234    | f

 1.234e-5 | f

(13 rows)

正如我现在看到的那样,科学计数法和负数仍然存在一些问题。


查看完整描述

3 回答

?
倚天杖

您可能会注意到,基于正则表达式的方法几乎不可能正确执行。例如,您的测试说这1.234e-5不是有效数字,而实际上是。另外,您错过了负数。如果某些东西看起来像数字,但是当您尝试存储它会导致溢出怎么办?


相反,我建议创建试图实际转换为的函数NUMERIC(或FLOAT如果您的任务需要它)并返回TRUE或FALSE取决于此转换是否成功的函数。


此代码将完全模拟功能ISNUMERIC():


CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$

DECLARE x NUMERIC;

BEGIN

    x = $1::NUMERIC;

    RETURN TRUE;

EXCEPTION WHEN others THEN

    RETURN FALSE;

END;

$$

STRICT

LANGUAGE plpgsql IMMUTABLE;

在您的数据上调用此函数将得到以下结果:


WITH test(x) AS ( VALUES (''), ('.'), ('.0'), ('0.'), ('0'), ('1'), ('123'),

  ('123.456'), ('abc'), ('1..2'), ('1.2.3.4'), ('1x234'), ('1.234e-5'))

SELECT x, isnumeric(x) FROM test;


    x     | isnumeric

----------+-----------

          | f

 .        | f

 .0       | t

 0.       | t

 0        | t

 1        | t

 123      | t

 123.456  | t

 abc      | f

 1..2     | f

 1.2.3.4  | f

 1x234    | f

 1.234e-5 | t

 (13 rows)

如果数据实际上是数字,它不仅更正确,更容易阅读,而且还可以更快地工作。



查看完整回答
反对 回复 2019-11-04
?
繁星coding

我想可能会有这样的看法(这不是对异常处理的滥用),但总的来说,我认为应该为此使用异常处理机制。测试字符串是否包含数字是正常处理的一部分,而不是“例外”。


但是您对不处理指数是正确的。这是正则表达式(下)的第二个刺。我必须追求使用正则表达式的解决方案的原因是,当遇到错误时给出指令退出时,此处提供为“正确”解决方案的解决方案将失败:


SET exit_on_error = true;


当运行SQL脚本组时,并且如果有任何问题/错误我们想立即停止时,我们经常使用它。给出此会话指令后,即使没有遇到“真实”异常,调用isnumeric的“正确”版本也会导致脚本立即退出。


create or replace function isnumeric(text) returns boolean

  immutable

  language plpgsql

as $$

begin

  if $1 is null or rtrim($1)='' then

    return false;

  else

    return (select $1 ~ '^ *[-+]?[0-9]*([.][0-9]+)?[0-9]*(([eE][-+]?)[0-9]+)? *$');

  end if;

end;

$$;



查看完整回答
反对 回复 2019-11-04
?
守着一只汪

您的问题是小数点两侧的两个0或多个[0-9]元素。您需要|在数字标识行中使用逻辑或:


~'^([0-9]+\.?[0-9]*|\.[0-9]+)$'

这将仅排除小数点作为有效数字。


查看完整回答
反对 回复 2019-11-04

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信