1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Function to calculate sum of all digits of a given number up to single digit (enhanced version of pr

Discussion in 'SQL PL/SQL' started by rajenb, May 30, 2014.

  1. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Purpose

    This code is a generic version of a function posted recently by Jagadekara to calculate the sum of all digits of a given number up to a single digit which was limited to a 12 digit number.

    This new function aims at removing this restriction and works for any number (within the range of valid integers in Oracle).


    The code

    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