How do I check to see if a value is an integer in MySQL?

Question

I see that within MySQL there are Cast() and Convert() functions to create integers from values, but is there any way to check to see if a value is an integer? Something like is_int() in PHP is what I am looking for.

Thanks!

Craig

Answer 1

Match it against a regular expression.

c.f. http://forums.mysql.com/read.php?60,1907,38488#msg-38488 as quoted below:

Re: IsNumeric() clause in MySQL??
Posted by: kevinclark ()
Date: August 08, 2005 01:01PM

I agree. Here is a function I created for MySQL 5:

CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint
RETURN sIn REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

This allows for an optional plus/minus sign at the beginning, one optional decimal point, and the rest numeric digits.

Answer 2

I'll assume you want to check a string value. One nice way is the REGEXP operator, matching the string to a regular expression. Simply do

select field from table where field REGEXP '^-?[0-9]+$';

this is reasonably fast. If your field is numeric, just test for

ceil(field) = field

instead.