IsNumeric() function in MySQL with convenient test script
As far as I know, MySQL does not have a built in function for determining if a string is a valid number or not.
I found the following solution for this, but the topic was closed. I wanted to add a test SQL statement and re-post this in case it's helpful to anyone else.
If the SQL Statement returns FAIL for any of the rows, this tells you immediately that the function has an issue (or that the expected value is incorrect). I actually often keep the test with the function as documentation of what the function does.
If someone has a better solution, this SQL Statement can be used to test alternatives to this function.
Here is the function:
===========================
create function isnumeric(val varchar(1024))
returns tinyint(1) deterministic
return val regexp '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';
Here is the TEST, with arbitrary values to test:
===========================
select val, expected, isnumeric(val) as actual, case when ifnull(expected,-1)=ifnull(isnumeric(val),-1) then '' else 'FAIL' end as Results from (
select '10' as val, true as expected union all
select '', false union all
select ' ', false union all
select ' ', false union all
select ' 1', false union all #Note: trim the string before to this function for this to pass.
select ' 1 ', false union all #Note: trim the string before to this function for this to pass.
select NULL, NULL union all
select '.', false union all # Note: this is the reason for a much longer regular expression
select '01', true union all
select '01.34', true union all
select '1.34', true union all
select '3.141592654', true union all
select '3.141592654141592654141592654141592654141592654141592654141592654141592654141592654141592654141592654141592654', true union all
select '+21.56', true union all
select '++21.56', false union all
select '-21.56', true union all
select '--21.56', false union all
select '21.56-', false union all
select '.00001', true union all
select '-.00001', true union all
select '+.00001', true union all
select '+.000a01', false union all
select '.00001b', false union all
select '10.1.' as val, false union all
select '10.1.2' as val, false union all
select '10.1.2.' as val, false union all
select '10.1.2.3' as val, false union all
select '001', true union all
select 'a001', false union all
select 'a', false union all
select 'ab001', false union all
select 'a1', false union all
select 'b1', false union all
select '001a', false union all
select 'david', false
) t1
Edited 2 time(s). Last edit at 11/13/2013 11:34AM by David Herpin.