Code (SQL): CREATE OR REPLACE FUNCTION XXST_SUM_OF_DIGITS(p_num NUMBER) RETURN NUMBER IS a NUMBER(12); b NUMBER; c NUMBER; d NUMBER; e NUMBER; BEGIN a:=P_NUM; b:= nvl(substr(a,1,1),0)+nvl(substr(a,2,1),0)+nvl(substr(a,3,1),0)+nvl(substr(a,4,1),0) +nvl(substr(a,5,1),0)+nvl(substr(a,6,1),0)+nvl(substr(a,7,1),0)+nvl(substr(a,8,1),0) +nvl(substr(a,9,1),0)+nvl(substr(a,10,1),0)+nvl(substr(a,11,1),0)+nvl(substr(a,12,1),0); c:= nvl(substr(b,1,1),0)+nvl(substr(b,2,1),0)+nvl(substr(b,3,1),0); d:= nvl(substr(c,1,1),0)+nvl(substr(c,2,1),0)+nvl(substr(c,3,1),0); e:= nvl(substr(d,1,1),0)+nvl(substr(d,2,1),0)+nvl(substr(d,3,1),0); RETURN(e); END; SELECT XXST_SUM_OF_DIGITS(123456789012) FROM dual; Ans: 3

Thanks for sharing Jagadekara. I had done a similar function during one of my assignments and have adapted it to generalize the function you've posted. You can view it at http://www.club-oracle.com/articles/function-to-calculate-sum-of-all-digits-of-given-number-upto-single-digit-941/

Hi, I can see it when I click on the link - but I think you (or any body else) can't see it because it is in status "Not Published". Anyway, here's the code in the meantime : Code (SQL): CREATE OR REPLACE FUNCTION SF_SUM_OF_DIGITS( p_num NUMBER) RETURN NUMBER IS l_sum NUMBER; l_len NUMBER := LENGTH(p_num); l_num NUMBER; BEGIN l_num := p_num; l_sum := p_num; WHILE (l_len > 1) LOOP l_sum := 0; FOR i IN 1 .. l_len LOOP l_sum := l_sum + SUBSTR(l_num, i, 1); END LOOP; l_len := LENGTH(l_sum); l_num := l_sum; END LOOP; RETURN(l_sum); END; / SELECT SF_SUM_OF_DIGITS(123456789012) FROM dual; SF_SUM_OF_DIGITS(123456789012) ------------------------------ 3 SELECT sf_sum_of_digits(1234) FROM dual; SF_SUM_OF_DIGITS(1234) ---------------------- 1 SELECT sf_sum_of_digits(5) FROM dual; SF_SUM_OF_DIGITS(5) ------------------- 5