IS_NUMERIC()

Question: How do I check if a string is numeric?

Answer: Call ISNUMERIC().

Question: No, that checks to see if the string is a number, it accepts all sorts of crap like '-1.5E+10'. I want to check if a string is numeric... just digits.

Answer: You mean, will the string work as an UNSIGNED BIGINT?

Question: Yeah, that'll work... actually, that's exactly what I want.

Answer: Then just call CAST() and if that doesn't throw an EXCEPTION then you're good to go.

Question: I don't want to accept the empty string or all spaces or NULL.

Answer: So you want it to work just like ISNUMERIC() but without all the crap?

Question: Yeah.

Answer: OK, here you go...

CREATE FUNCTION IS_NUMERIC
   ( IN @string  LONG VARCHAR )
   RETURNS INTEGER 
BEGIN

DECLARE @unsigned_bigint  UNSIGNED BIGINT;

IF TRIM ( COALESCE ( @string, '' ) ) = '' THEN
   RETURN 0;
END IF;

SET @unsigned_bigint = CAST ( @string AS UNSIGNED BIGINT );

RETURN 1;

EXCEPTION WHEN OTHERS THEN
   RETURN 0;

END;

Here are some test cases:

SELECT CAST ( NULL AS LONG VARCHAR ) as x,  ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '' as x,     ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT ' ' as x,    ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '    9    ' as x,  ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '-1' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '-1.5E+10' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '00018446744073709551615' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '1' as x,   ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '1.0' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '1.1' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '18446744073709551615' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '18446744073709551616' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '9223372036854775807' as x,  ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '9223372036854775808' as x,  ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT 'abc' as x,  ISNUMERIC ( x ), IS_NUMERIC ( x )
ORDER BY 1;

                         ISNUMERIC(x)
                         |   IS_NUMERIC(x)
                         |   |
x                        V   V
----------------------- --- --- 
(NULL)                   0   0
                         0   0
                         0   0
    9                    1   1
-1                       1   0
-1.5E+10                 1   0
00018446744073709551615  1   1
1                        1   1
1.0                      1   0
1.1                      1   0
18446744073709551615     1   1
18446744073709551616     1   0
9223372036854775807      1   1
9223372036854775808      1   1
abc                      0   0

---