Convert taka number to word

Convert Number to Letter Word in Oracle

Topic Introduction: In any invoice or commercial we need to show money in words. as usual we use numbers to calculate money. In this tutorial, we will show Convert Number to Letter Word in Oracle thats the money amount in words from any number. If we want to show the amount in words this query or function can be helpful.

Function for Conversion of the number to word

A function can be used for converting the number amounts into words. The Function code is given below.

CREATE OR REPLACE FUNCTION amount_in_words (i_amt IN NUMBER)

   RETURN VARCHAR2

IS

   n_dollar   NUMBER;

   n_cents    NUMBER;

   FUNCTION check_if_single (i_num IN NUMBER, currency IN VARCHAR2)

      RETURN VARCHAR2

   IS

      FUNCTION n_spell (i_num IN NUMBER)

         RETURN VARCHAR2

      AS

         TYPE w_Array IS TABLE OF VARCHAR2 (255);

         l_str w_array

               := w_array ('',

                           ' thousand ',

                           ' million ',

                           ' billion ',

                           ' trillion ',

                           ' quadrillion ',

                           ' quintillion ',

                           ' sextillion ',

                           ' septillion ',

                           ' octillion ',

                           ' nonillion ',

                           ' decillion ',

                           ' undecillion ',

                           ' duodecillion ');

         l_num           VARCHAR2 (50) DEFAULT TRUNC (i_num);

         l_is_negative   BOOLEAN := FALSE;

         l_return        VARCHAR2 (4000);

      BEGIN

         IF SIGN (i_num) = -1

         THEN

            l_is_negative := TRUE;

            l_num := TRUNC (ABS (i_num));

         END IF;

         FOR i IN 1 .. l_str.COUNT

         LOOP

            EXIT WHEN l_num IS NULL;

            IF (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;

         IF NOT l_is_negative

         THEN

            RETURN INITCAP (l_return);

         ELSE

            RETURN 'Negative ' || INITCAP (l_return);

         END IF;

      END n_spell;

   BEGIN

      IF i_num = 1

      THEN

         RETURN 'One ' || currency;

      ELSE

         RETURN n_spell (i_num) || ' ' || currency;

      END IF;

   END check_if_single;

BEGIN

   IF i_amt IS NULL

   THEN

      RETURN '';

   END IF;

   n_dollar := TRUNC (i_amt);

   n_cents := (ABS (i_amt) - TRUNC (ABS (i_amt))) * 100;

   IF NVL (n_cents, 0) > 0

   THEN

      RETURN    check_if_single (n_dollar, 'Taka')

             || ' and '

             || check_if_single (n_cents, 'Poisha');

   ELSE

      RETURN check_if_single (n_dollar, 'Taka');

   END IF;

END amount_in_words;

/

SQL Query uses that function to Convert Numbers to a word Calculator.

select amount_in_words(12225039)

from dual;

The results will be below in the picture shown.