1 2 3 Previous Next 69 Replies Latest reply on Feb 26, 2013 12:25 PM by IckyIckyChiMoon

# PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP

Hello,

Let's say that I have a number 6718 and I want to add all of its numbers and reach just a single number. For example:

6 + 7 + 1 + 8 = 22 then

2 + 2 = 4

This could take more steps if the number were larger.

• ###### 1. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
Hi,
you can use something like this:
``````select sum(mod(trunc((6718/ power(10, level-1))), 10))
from dual
connect by level <= length(6718);

or

select  sum(REGEXP_SUBSTR(6718, '[[:digit:]]', 1, level))
from dual
connect by level <= length(6718);``````
• ###### 2. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
Hello

thanks for your reply but these 2 sqls return 22. I need 4 (22 => 2 + 2). This doesn't have to be a sql, it can also be plsql with a for loop.
• ###### 3. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
Implement this logic in the loop
``````if  num > 0
then
temp:= mod(num,10);
num:=round(num/10);
sum := temp + sum;
else
DBMS_OUTPUT.PUT_LINE(sum);
end if;``````
• ###### 4. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
user12924211 wrote:
Urgent? Really?
In that case you must be referring to a live commercial application, in which case the forum is not the place to ask such commercially urgent issues. You need to raise a Service Request with Oracle Support.

• ###### 5. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
Something like this: (Looks like recursive approach)
``````CREATE OR REPLACE FUNCTION fn_ret_int_sum (num NUMBER)
RETURN NUMBER AS
total   NUMBER;
total1   NUMBER;
BEGIN
SELECT SUM (MOD (TRUNC ( (num / POWER (10, LEVEL - 1))), 10)) t
INTO total
FROM DUAL
CONNECT BY LEVEL <= LENGTH (num);

IF (LENGTH (total) > 1) THEN
total1 := fn_ret_int_sum (total);
END IF;

END;
/``````
output:
``````select fn_ret_int_sum(671899199988888) from dual;

----- returns 9``````
I suggest if you can think of SQL solution rather than this function approach then it would be faster.. Anyways lets wait for experts to answer this in plain SQL.

Cheers,
Manik.
• ###### 6. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
Modified version :)
``````declare
num  number:= 6718;
begin
while length(num) > 1
loop
select  sum(REGEXP_SUBSTR(num, '[[:digit:]]', 1, level))
into num
from dual
connect by level <= length(num);
end loop;
dbms_output.put_line(num);
end;``````
• ###### 7. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
I also implemented a logic like that but it also returns 22, I need only one-digit output which is 4 in this case

my implementation:

LOOP
nAmorti := nAmorti + MOD (nWinningNumber, 10);
nWinningNumber := FLOOR (nWinningNumber / 10);
EXIT WHEN nWinningNumber <= 0;
END LOOP;
• ###### 8. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
Just have a look at my proposed solution above just as a template to solve your purpose.
I admit that I did not test it thoroughly.. Check if its fine.....

Cheers,
Manik.
• ###### 9. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
Thank you! this works just fine.
• ###### 10. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
in pl/sql:
``````declare
v_in number := 6718;

function sum_digits(l_in in out number)
return number
is
l_sum number := 0;
begin
for i in 1..ceil(log(10,l_in))
loop
l_sum := l_sum + floor(mod(l_in,power(10,i))/power(10,i-1));
end loop;
return l_sum;
end sum_digits;
begin
while v_in >= 10 loop
v_in := sum_digits (v_in);
end loop;
dbms_output.put_line (to_char(v_in));
end;
/``````
?
• ###### 11. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
This should work

``````set serveroutput on
DECLARE
temp number :=0;
num1  NUMBER :=123;
sum1  number :=0;
BEGIN

loop
if num1 > 0
then
temp := mod(num1,10);
num1 := round(num1/10);
sum1 := temp + sum1;
else
null;
end IF;
EXIT WHEN num1 <= 0;
end loop;
DBMS_OUTPUT.PUT_LINE(SUM1);

END;``````
• ###### 12. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
THANK YOU ALL VERY MUCH FOR YOUR SOLUTIONS
• ###### 13. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
Rahul India wrote:
This should work

``````set serveroutput on
DECLARE
temp number :=0;
num1  NUMBER :=123;
sum1  number :=0;
BEGIN

loop
if num1 > 0
then
temp := mod(num1,10);
num1 := round(num1/10);
sum1 := temp + sum1;
else
null;
end IF;
EXIT WHEN num1 <= 0;
end loop;
DBMS_OUTPUT.PUT_LINE(SUM1);

END;``````
No Rahul, try to understand the requirement again: try giving 12399999 as input to your code....it gives 16 actually OP expects that 16 to be re-calculated as 1+6 =7.

Cheers,
Manik.
• ###### 14. Re: PLSQL ADDING ALL NUMBERS OF A SINGLE NUMBER HELP
hmm ok got your point ;)
Thanks
1 2 3 Previous Next