What if something looks like a number, but when you try to store it it will cause overflow? For example, your test says that 1.234e-5 is not valid number, when it really is.
There are still some issues with the scientific notation and with negative numbers, as I see now.Īs you may noticed, regex-based method is almost impossible to do correctly.
#Postgresql regex update
Update Based on this answer (and its comments), I adapted the pattern to: WITH test(x) AS ( I can't get any closer to this at the moment. ('123.456'), ('abc'), ('1.2'), ('1.2.3.4'))Īs you can see, the first two items (the empty string '' and the sole period '.') are misclassified as being a numeric type (which they are not). And so I adapted the statement given in this place to incorporate floating point numbers. I found that Postgres' pattern matching could be used for this. As in the following: SELECT AVG(CASE WHEN x ~ '^*.?*$' THEN x::float ELSE NULL END) FROM test I need to determine whether a given string can be interpreted as a number (integer or floating point) in an SQL statement.
0 kommentar(er)
