IsNumeric() clause in MySQL??

[So Nakagawa]

THis is originally posted by Cai Black (http://forums.mysql.com/read.php?60,1907,241284#msg-241284). However I found that his logic is not quite complete.


"--------------------------------------------------------------------------
Oops! Henri's does work fine. My enhancements then are the following:

* removing the space between the function name and the parentheses
* adding the size to the datatype: TINYINT(1)
* indicating that the function is deterministic: DETERMINISTIC
* replacing the range '{0,1}' with a '?'


CREATE FUNCTION ISNUMERIC(myVal VARCHAR(1024))
RETURNS TINYINT(1) DETERMINISTIC
RETURN myVal REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';


Sorry for any confusion that my previous post may have caused.
--------------------------------------------------------------------------"

This logic will not work with numbers like 2.5e+008.
So the code should be like

CREATE FUNCTION ISNUMERIC(myVal VARCHAR(1024))
RETURNS TINYINT(1) DETERMINISTIC
RETURN myVal REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+|[0-9]+\\.[0-9]*e\\+[0-9]+|[0-9]*\\.[0-9]+e\\+[0-9]+|[0-9]+e\\+[0-9]+)$';