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.