regexp_replace - regexp_substr postgresql

isnumeric() con PostgreSQL

Question

Necesito determinar si una cadena dada se puede interpretar como un número (entero o punto flotante) en una declaración de SQL. Como en lo siguiente:

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

Descubrí que la coincidencia de patrones de Postgres podría usarse para esto. Y entonces adapté la declaración dada en este lugar para incorporar números de coma flotante. Este es mi código:

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;

La salida:

    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)

Como puede ver, los dos primeros elementos (la cadena vacía '''' y el único período ''.'' ) Se clasifican erróneamente como de tipo numérico (que no lo son). No puedo acercarme más a esto en este momento. Cualquier ayuda apreciada!

Actualización Basándome en esta respuesta (y sus comentarios), adapté el patrón a:

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;

Lo que da:

     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)

Todavía hay algunos problemas con la notación científica y con números negativos, como veo ahora.

Answer 1

Como habrás notado, el método basado en expresiones regulares es casi imposible de hacer correctamente. Por ejemplo, su prueba dice que 1.234e-5 no es un número válido, cuando realmente lo es. Además, te perdiste números negativos. ¿Qué pasa si algo se parece a un número, pero cuando intenta almacenarlo causará un desbordamiento?

En cambio, recomendaría crear una función que intente realmente convertir a NUMERIC (o FLOAT si su tarea así lo requiere) y devuelve TRUE o FALSE dependiendo de si este lanzamiento fue exitoso o no.

Este código simulará completamente la función 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;

Al activar esta función en sus datos obtiene los siguientes resultados:

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)

No solo es más correcto y fácil de leer, también funcionará más rápido si los datos fueran realmente un número.

Answer 2

Tu problema son los dos 0 o más elementos [0-9] en cada lado del punto decimal. Necesita usar un OR lógico | en la línea de identificación de número:

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

Esto excluirá un punto decimal solo como un número válido.