Monday, July 23, 2012

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


2 comments:

Jeff Albion said...

Question: How do I check if a string is numeric?
...
Answer: You mean, will the string work as an UNSIGNED BIGINT?
Question: Yeah, that'll work... actually, that's exactly what I want.

No, it's not! The original question asked for a string check of numeric digits. Regular Expressions should instantly come to mind:


CREATE OR REPLACE FUNCTION IS_NUMERIC
( IN @string LONG VARCHAR )
RETURNS INTEGER
BEGIN
IF REGEXP_SUBSTR(TRIM(COALESCE(@string, '')), '^[0-9]+$') IS NULL THEN
RETURN 0;
ELSE
RETURN 1;
END IF;
END;


This then properly handles the case 18446744073709551616 (and larger) as "1".

Breck Carter said...

Sure of what was asked, you are, but sure of what was wanted are you, hmm?

Instantly to mind, should regular expressions come, hmm? Hmmmmmm.