/ 猿问

# PostgreSQL的isnumeric（）

2019-11-03 04:00:23

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

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 回答

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)

SET exit_on_error = true;

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;

\$\$;

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

• 3 回答
• 0 关注
• 184 浏览

0/150