I was trying to write a MySQL stored procedure today that was crying out for the is_numeric function – the one that doesn’t exist. I wanted to select two different things depending on whether the value in a field was numeric or not.
Luckily trying to convert a non integer into an integer returns a 0 (at least in the version I am running it does). Hence testing for CONVERT(field_value,SIGNED INTEGER)>0 is pretty close to what I’d expect from the missing is_numeric(field_value). In my case it is good enough as I know that I am dealing with integers not decimals, and none of them are 0!
Job done!
NB: MySQL version 5.0.22.
October 21, 2009 at 10:09 am
Hi! I use REGEXP to know if is numeric or not:
WHERE FIELD NOT REGEXP ‘^[0-9]+$’
^: Start with
[0-9]: Numbers
+: any numbers (1 or more times)
$: to the end
regards!
Marc
October 21, 2009 at 10:10 am
I want to add to your comment that:
CONVERT(field_value,SIGNED INTEGER)>0
in few cases return a cleaned string, for example:
1h becomes 1
299*123 becomes 299
see you!