IsNumeric() clause in MySQL??

IsNumeric() clause in MySQL??
Posted by: Cassiano ()
Date: September 10, 2004 12:08PM

How can i test if a field has a numeric data type, at SQLServer i use IsNumeric() clause, what can i use in MySQL?
============================
Re: IsNumeric() clause in MySQL??
Posted by: Homam Alsayed ()
Date: February 02, 2005 01:37PM

The condition:

WHERE IsNumeric(SomeColumn) = 1

is bascially equivalent to:

WHERE CONVERT(SomeColumn, SIGNED INTEGER) IS NOT NULL

The only snag is you can only check for integers.
=============================

Re: IsNumeric() clause in MySQL??
Posted by: Cai Black ()
Date: January 07, 2009 11:34AM

Oops! Henri's does work fine. My enhancements then are the following:

* removing the space between the function name and the parentheses
* adding the size to the datatype: TINYINT(1)
* indicating that the function is deterministic: DETERMINISTIC
* replacing the range '{0,1}' with a '?'

CREATE FUNCTION ISNUMERIC(myVal VARCHAR(1024))
RETURNS TINYINT(1) DETERMINISTIC
RETURN myVal REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

Sorry for any confusion that my previous post may have caused.
---
Cai Black



Edited 1 time(s). Last edit at 01/07/2009 11:42AM by Cai Black.