Any Oracle Function Comparitive to : isnumber ?

[aahan]

Hi,

Is there any function available or any shorter / quicker way of finding out in a varchar2 column if the value is a number or not ?

Rows in a varchar2 column may have character values or numeric values. Any way of alienating the only numeric rows.

Thanks

 

[jee]

I have always used the TO_NUMBER function. If this is successful, the string is a valid number. If it fails, the string is not a valid number (error code is ORA-01722).

 

[sfvb]

If you looking for whole positive numbers you could use:

IF replace(translate(my_variable, '1234567890', '          '), ' ') IS NULL THEN

 

[lewisp]

If you're using pl/sql you could write a function like this:

FUNCTION isnumber (p_str IN VARCHAR2) RETURN BOOLEAN IS
l_test NUMBER;
BEGIN
l_test := To_Number(p_str);
Return TRUE;
EXCEPTION
WHEN OTHERS THEN
Return FALSE;
END;

 

[aahan]

Hi,

Thank you very much for the responses. I created a function in PL/SQL to check it.

Thanks again