Stored Routine: Finds whether the given string is numeric

MySQL Tips and Tricks

Stored Routine: Finds whether the given string is numeric

Numeric strings consist of optional sign, any number of digits, optional decimal part and optional exponential part. Thus +0123.45e6 is a valid numeric value. Hexadecimal notation (0xFF) is allowed too but only without sign, decimal and exponential part.

Usage: CALL is_numeric('string to be checked');

DELIMITER $$

CREATE FUNCTION `is_numeric`( str VARCHAR(256) ) RETURNS BOOL
BEGIN
  RETURN str REGEXP('^([+-]?[0-9]+\.?[0-9]*e?[0-9]+)|(0x[0-9A-F]+)$');
END $$

DELIMITER ;