Wednesday, July 25, 2012

Mysql function to check a value is numeric or not

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 -

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
   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


Leave a Reply