Wednesday, July 25, 2012

Mysql function to check a value is numeric or not

0 comments
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 :-

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




  

Leave a Reply

Labels