| Need to sum a char field in SQLPLUS [message #365775] |
Tue, 25 January 2000 16:41  |
Gayle Pavlik
Messages: 3
Registered: January 2000
|
Junior Member |
|
|
|
I have a field that is character field, length 15. Users want a sum of the column. It contains both numeric and/or characters. Get error 01722 if try to use to_number and comes across characters. If field contains numers need to sum, otherwise 0. Any way to do this short of using decode to test each of the 15 characters and check if number 0 through 9?
|
|
|
|
| Re: Need to sum a char field in SQLPLUS [message #365781 is a reply to message #365775] |
Fri, 28 January 2000 13:10  |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
You make a little function, and then it must work.
ex:
CREATE OR REPLACE FUNCTION isitanumber
(in_string IN VARCHAR2) RETURN NUMBER
IS
to_val NUMBER;
BEGIN
to_val := TO_NUMBER (in_string);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN RETURN 0;
END;
/
select decode(isitanumber(var1),1,var1,0)
from table;
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
