Detect if value is number in MySQL?

Question

Is there a way to detect if a value is a number in a MySQL query? e.g., `SELECT * FROM myTable WHERE isANumber(col1)=true`

Answer 1

Something like this would check for an optional leading plus or minus, followed by only digits up until at most one decimal point and then more digits.

CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint

RETURN sIn REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

Answer 2

There is no built-in MySQL function to determine if an expression is numeric, but, using regular expressions, you can create a user-defined function that will do the job. The following can be found at the URL

https://www.experts-exchange.com/articles/252/How-to-Mimic-the-T-SQL-IsNumeric-Function-in-MySQL.html :

MySQL IsNumeric() User Defined Function (UDF).

IsNumeric() UDF Code.

delimiter $$

drop function if exists `isnumeric` $$

create function `isnumeric` (s varchar(255)) returns int

begin

set @match =

'^(([0-9+-.$]{1})|([+-]?[$]?[0-9]*(([.]{1}[0-9]*)|([.]?[0-9]+))))$';

return if(s regexp @match, 1, 0);

end $$

delimiter ;