Re: FUNCTION in PL/SQL

From: Ken Denny <kdenny_at_interpath.com>
Date: 1997/06/16
Message-ID: <33A59208.1AE9@interpath.com>#1/1

Acáacio Nuno Loureiro de Jesus wrote:
>
> Hi!
> If you have a function in PL/SQL that converts a number into it's
> designation like
> for example : 1 returns 'one'
> 10 returns 'ten'
> 234 returns 'two hundred and thirty four'
> Please send me.
> Thanks!
>
> Acáacio.

Hi

I had been wanting to write one of these and your post prompted me to do it. Here it is:

create or replace
FUNCTION wordnum(inparam integer) RETURN VARCHAR2 IS

   TYPE ones IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;    TYPE tens IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER;    one_to_19 ones;
   tensdigs tens;
   nextdig integer;
   resultstring VARCHAR2(120);
   charnum VARCHAR2(10);
BEGIN
   IF inparam >= 100000000 THEN

      RETURN 'Number input must be less than one billion';    END IF;
   IF inparam = 0 THEN

      RETURN 'ZERO';
   END IF;

   one_to_19(1) := 'ONE';
   one_to_19(2) := 'TWO';
   one_to_19(3) := 'THREE';
   one_to_19(4) := 'FOUR';
   one_to_19(5) := 'FIVE';
   one_to_19(6) := 'SIX';
   one_to_19(7) := 'SEVEN';
   one_to_19(8) := 'EIGHT';
   one_to_19(9) := 'NINE';
   one_to_19(10) := 'TEN';
   one_to_19(11) := 'ELEVEN';
   one_to_19(12) := 'TWELVE';
   one_to_19(13) := 'THIRTEEN';
   one_to_19(14) := 'FOURTEEN';
   one_to_19(15) := 'FIFTEEN';
   one_to_19(16) := 'SIXTEEN';
   one_to_19(17) := 'SEVENTEEN';
   one_to_19(18) := 'EIGHTEEN';
   one_to_19(19) := 'NINETEEN';
   tensdigs(2) := 'TWENTY';
   tensdigs(3) := 'THIRTY';
   tensdigs(4) := 'FOURTY';
   tensdigs(5) := 'FIFTY';
   tensdigs(6) := 'SIXTY';
   tensdigs(7) := 'SEVENTY';

   tensdigs(8) := 'EIGHTY';
   tensdigs(9) := 'NINETY';
   charnum := TO_CHAR(inparam,'000000000');    FOR i IN 2..4 LOOP
/*
|| Because the to_char function always puts a leading space in when || a format mask is used
*/

      nextdig := 2 + (i-2)*3;
      IF SUBSTR(charnum,nextdig,3) != '000' THEN
         IF SUBSTR(charnum,nextdig,1) != 0 THEN
            resultstring := resultstring || ' ' ||
               one_to_19(TO_NUMBER(SUBSTR(charnum,nextdig,1))) ||
               ' HUNDRED';
         END IF;
         IF TO_NUMBER(SUBSTR(charnum,nextdig+1,1)) > 1 THEN
            resultstring := resultstring || ' ' ||
               tensdigs(TO_NUMBER(SUBSTR(charnum,nextdig+1,1)));
            IF SUBSTR(charnum,nextdig+2,1) != '0' THEN
               resultstring := resultstring || '-' ||
               one_to_19(TO_NUMBER(SUBSTR(charnum,nextdig+2,1)));
            END IF;
         ELSIF SUBSTR(charnum,nextdig+1,2) != '00' THEN
            resultstring := resultstring || ' ' ||
               one_to_19(TO_NUMBER(SUBSTR(charnum,nextdig+1,2)));
         END IF;
         IF i = 2 THEN
            resultstring := resultstring || ' MILLION';
         ELSIF i =3 THEN
            resultstring := resultstring || ' THOUSAND';
         END IF;
      END IF;

   END LOOP;
   RETURN resultstring;
END;
/

Shall I demonstrate:

SQL> select wordnum(717022) from dual;

WORDNUM(717022)

---

 SEVEN HUNDRED SEVENTEEN THOUSAND TWENTY-TWO SQL> select wordnum(82736492) from dual;

WORDNUM(82736492)

---

 EIGHTY-TWO MILLION SEVEN HUNDRED THIRTY-SIX THOUSAND FOUR HUNDRED NINETY-TWO The number must be between 0 and 999999999

Good luck
Ken Denny
kdenny_at_interpath.com Received on Mon Jun 16 1997 - 00:00:00 CDT