| isnumeric [message #37701] |
Thu, 21 February 2002 23:26  |
Hans Neckebroeck
Messages: 2
Registered: February 2002
|
Junior Member |
|
|
I'm looking for a function (or a workaround) like there is one in SQL-server, namely ISNUMERIC.
regards,
Hans
|
|
|
|
| Re: isnumeric [message #37703 is a reply to message #37701] |
Fri, 22 February 2002 03:38  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
The following will work, but it has some anomolies. For instance, if the field is 1305-1204 in it, Oracle will consider it a number (it subtracts and evaluates). An alternative is to test each character using LENGTH and SUBSTR for between 0 and 9.
CREATE OR REPLACE FUNCTION is_numeric ( i_string VARCHAR2) RETURN INTEGER
AS
v_test_num NUMBER;
BEGIN
v_test_num := TO_NUMBER(i_string);
RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
RETURN 0;
WHEN OTHERS THEN
RAISE;
END is_numeric;
|
|
|
|
| Re: isnumeric [message #37706 is a reply to message #37701] |
Fri, 22 February 2002 03:49 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
Here's the alternative:
CREATE OR REPLACE FUNCTION is_numeric ( i_string VARCHAR2) RETURN INTEGER
AS
BEGIN
FOR I IN 1..LENGTH(i_string)
LOOP
IF SUBSTR(i_string,i,1) between '0' and '9'
THEN NULL;
ELSE
RETURN 0;
END IF;
END LOOP;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END is_numeric;
|
|
|
|
| Re: isnumeric [message #38367 is a reply to message #37701] |
Mon, 15 April 2002 13:55 |
KAW
Messages: 8
Registered: February 2001
|
Junior Member |
|
|
I put this code out here a year ago.
IsEmpty should also be out there.
FUNCTION IsNumeric(P_Value IN VARCHAR2)
Return Boolean
IS
V_Numeric_Value NUMBER;
Begin
SELECT To_Number(P_Value) INTO V_Numeric_Value FROM Dual;
RETURN TRUE;
EXCEPTION
WHEN Invalid_Number THEN
Return FALSE;
End IsNumeric; -- End IsNumeric
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
