In Mysql 5.1 I need a function which would return true if a column value was Numeric and false if it was not.
I searched the internet and found the link -
http://stackoverflow.com/questions/2756736/how-to-only-select-numeric-data-from-mysql
It gave me a good idea about how to do it. Then I wrote my one as follows :-
Description -
I searched the internet and found the link -
http://stackoverflow.com/questions/2756736/how-to-only-select-numeric-data-from-mysql
It gave me a good idea about how to do it. Then I wrote my one as follows :-
CREATE FUNCTION is_numeric (sIn text) RETURNS tinyint
DETERMINISTIC
NO SQL
RETURN sIn REGEXP '^-*[[:digit:]]+(\.[[:digit:]]+)?$' ;
Description -
- ^ = match at the begining of the string
- $ = match at the end of the string
- [:digit:] = digit class
- ^[[:digit:]]+$ = from the begining to the end only digit characters
- -* = 0 or more occurrances of - (minus sign)
- (\.[[:digit:]]+)? = if a .(dot) is used then there must be 1 or more digit after that and by using (..)?, we ensured 0 or more occurrences of the item