Home » SQL & PL/SQL » SQL & PL/SQL » Need to sum a char field in SQLPLUS
Need to sum a char field in SQLPLUS [message #365775] Tue, 25 January 2000 16:41 Go to next message
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 Go to previous message
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;
Previous Topic: New PLSQL Message Board
Next Topic: How to find sql statements executed in a session
Goto Forum:
  


Current Time: Tue Jun 22 00:49:19 CDT 2021