Home » RDBMS Server » Server Administration » Need to sum char field in SQLPLUS
Need to sum char field in SQLPLUS [message #370731] Tue, 25 January 2000 17:53 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 char field in SQLPLUS [message #370737 is a reply to message #370731] Wed, 26 January 2000 10:01 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Gayle,

The quick & dirty answer is that you will either need to use decode, or a function you write, to do this. That said, I'm not 100% clear on WHAT you really want to do - are you talking about summing multiple rows, grouped by another field (as in SELECT a, SUM(b) FROM xx GROUP BY a) and, if so, are you saying that any row that contains characters should make the sum 0 or that each row containing characters should itself be counted as 0 in the sum. Or, are you saying that for each row, if the individual characters are all numeric, sum the value of the single digits (sort of like a checksum), otherwise return 0. Or are you asking for something else all together.

Beyond this, it might be worth explaining to the 'powers-that-be' that there is a tremendous amount of overhead involved in doing this kind of per-record validation in a query and that the best solution is to segregate numeric and non-numeric data into separate fields or, if this is impossible, to add an additional 'flag' field to the table(s) involved to hold a datatype identifier. The second method would allow you to run a function once to populate this field for your existing data, then incorporate the function logic (or the function itself) into a database trigger that would validate each row inserted and updated. This would simplify, and therefore speed up, retrieval. It is still the 'second best' way to solve the problem because you still must convert the numerics to numbers individually.

Hope this helps,
Paul
Re: Need to sum char field in SQLPLUS [message #370741 is a reply to message #370731] Thu, 27 January 2000 07:09 Go to previous messageGo to next message
Atavur Rahaman S.A
Messages: 23
Registered: January 2000
Junior Member
Hi Gayli Pavlik,
The worst and labour process is to use TRANSLATE and SUBSTR functions. Hope so this will definetly work with any type of possible combinations. It's really dirty but worth for your query.
This will be helpful only if the VSIZE of the column is 15. You have to use it for 15 times.

select ltrim(to_char(
to_number(nvl(substr(ltrim(translate(dummy,'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+=*-/~><?',' )),1,1),0))<BR >+
...
...
.
.
.+
to_number(nvl(substr(ltrim(translate(dummy,'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+=*-/~><?',' )),15,1),0)))) FROM

Best regards

Atavur Rahaman S.A
Re: Need to sum char field in SQLPLUS [message #370744 is a reply to message #370731] Fri, 28 January 2000 13:12 Go to previous message
Thierry Van der Auwera
Messages: 44
Registered: January 2000
Member
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: Inverting a table using SQL
Next Topic: inserting a date
Goto Forum:
  


Current Time: Fri Oct 30 03:33:30 CDT 2020