带PostgreSQL的isnumeric()

Question

我需要确定给定的字符串是否可以解释为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)

正如我现在所看到的，科学表示法和负数仍然存在一些问题。

Answer 1

正如您可能注意到的，基于正则表达式的方法几乎不可能正确地完成。例如，您的测试表明，当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)

不仅它更正确，更容易阅读，如果数据实际上是一个数字，它也将工作得更快。

Answer 2

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

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

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

Answer 3

我想人们可能会有这样的看法(即它不是滥用异常处理)，但通常我认为应该为此使用异常处理机制。测试一个字符串是否包含数字是正常处理的一部分，并不是“例外”。

但你不处理指数是对的。下面是正则表达式的第二次尝试(如下所示)。我必须寻求一个使用正则表达式的解决方案的原因是，当遇到错误时指令被赋予退出时，作为“正确”解决方案提供的解决方案将失败：

SET exit_on_error = true;

在运行SQL脚本组时，以及在出现任何问题/错误时，我们经常使用这种方法。当发出此会话指令时，调用is数值的“正确”版本将导致脚本立即退出，即使没有遇到“真正的”异常。

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;
$$;