Don't you think Oracle would have made ISNUMBER, ISDATE, etc.? Other databases
have these functions.

Anyway, here is a "ISNUMBER" function you can use:

select instr(translate(column_name,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
	'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM table_name;

It returns 0 if it is a number, 1 if it is not.

This works if the field has only characters or digits. For example, having !!!
as a string would not work in this case. If you care about other characters,
change the translate to add those characters.

-Ari
____________________________________________________________________________________
Date: Tue, 13 Nov 2001 17:46:42 -0500
From: VIJAY JAGDALE 
To: ari@pocketdba.com
Subject: improved ISNUMBER function (tip 403)

Hi Ari,

I had contributed to one of your earlier tips(#255). Here is another better way
of doing tip #403 (ISNUMBER)

select length(translate(trim(column_name),' +-.0123456789',' ')) from dual

will give you a zero if it is a number or greater than zero if not numeric
(actually gives the count of non numeric characters)

NOTE THERE IS A SPACE BEFORE THE PLUS in the second parameter of the translate,
and a single space inthe last option.

This method has the advantege that it does not care what other characters you
have in the field... you can even have unprintable characters.

The additional TRIM functions gets rid leading or trailing spaces.

Also note that if you want the accountants representation of negative numbers,
you can also add parenthesis to the second parameter.

regards,

Vijay Jagdale
Technical Systems Specialist
Michigan Department of Transportation
jagdalev@mdot.state.mi.us


Back to Ari Kaplan's Home Page