MySQL Forums
Forum List  »  Microsoft SQL Server

IsNumeric() function in MySQL with convenient test script
Posted by: David Herpin
Date: November 13, 2013 11:17AM

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.

Options: ReplyQuote


Subject
Written By
Posted
IsNumeric() function in MySQL with convenient test script
November 13, 2013 11:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.