0

I have this function that converts a number (amount) to words, I understand the result of this function, but I don't know how it works, as I don't know how to convert the loop into SQL

 FUNCTION number_to_word(p_number IN NUMBER) RETURN VARCHAR2 AS
    TYPE myArray IS TABLE OF VARCHAR2(255);
    l_str myArray := myArray('',
                             ' thousand ',
                             ' million ',
                             ' billion ',
                             ' trillion ',
                             ' quadrillion ',
                             ' quintillion ',
                             ' sextillion ',
                             ' septillion ',
                             ' octillion ',
                             ' nonillion ',
                             ' decillion ',
                             ' undecillion ',
                             ' duodecillion ');

    l_num    VARCHAR2(50) DEFAULT trunc(p_number);
    l_return VARCHAR2(32767);
  BEGIN

    IF (l_num IS NULL OR l_num = 0) THEN
      l_return := 'Zero';
    ELSE
      FOR i IN 1 .. l_str.count LOOP
        EXIT WHEN l_num IS NULL;

        IF (to_number(substr(l_num, length(l_num) - 2, 3)) <> 0) THEN
          l_return := to_char(to_date(substr(l_num, length(l_num) - 2, 3),
                                      'J'),
                              'Jsp') || l_str(i) || l_return;
        END IF;
        l_num := substr(l_num, 1, length(l_num) - 3);
      END LOOP;
    END IF;

    RETURN l_return;
  END number_to_word;
Aasem Shoshari
  • 37
  • 1
  • 7
  • You probably need a recursive cte. – jarlh Feb 22 '23 at 15:45
  • 2
    Why don't you want to use a function? (If you can't create a stored one or this is a one-off and you don't need it to be permanent, from 12c [you can define a local function in a `with` clause](https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1#functions). Or there's a SQL version [in this answer](https://stackoverflow.com/a/36064158/266304). ) – Alex Poole Feb 22 '23 at 16:04
  • i work on oracle fusion, what i understood is that we can't use functions in the instance im working on, so i need to convert it to ```sql``` – Aasem Shoshari Feb 22 '23 at 16:12
  • See this link for further information https://www.viralpatel.net/convert-number-into-words-oracle-sql-query/amp/ – Beefstu Feb 22 '23 at 16:14
  • 1
    Added another SQL version to [my answer to a previous question](https://stackoverflow.com/a/36064158/1509264) that @AlexPoole linked to. – MT0 Feb 22 '23 at 16:48

0 Answers0