convert number to currency 3

[bookouri]

Im have a column number(9,2). This column contains dollar amounts, like 1.50, 2.75, 2, etc. Im converting these amounts to spell them out like on a check. Ive got this far:

select debit, decode(sign(substr(debit, 1, (instr(debit,'.')-1))),-1,'Negative', 0, 'Zero', NULL)||
decode(sign(abs(substr(debit, 1, (instr(debit,'.')-1)))),+1,to_char(to_date(abs(substr(debit, 1, (instr(debit,'.')-1))),'J'),'Jsp'))||' Dollars' dollars
from gaccounts
where debit is not null

The problem i have now is that the select doesnt work if the col contains a whole dollar amount and no decimal for the instr to find. Im starting to think that by the time i work that out and THEN handle the right side of the decimal that this solution will be too convoluted. Can anybody point me to a simpler solution?

 

[jdemmi]

select to_char(to_date(substr(873.25,1,instr(873.25,'.',1)-1),'J'), 'JSP') || ' DOLLARS AND ' || to_char(to_date(substr(873.25,instr(873.25,'.',1)+1),'J'), 'JSP') || ' CENTS'
as currency_text from dual;


you should be able to replace 873.25 with your field and dual with your table.

I got part of this here...

http://www.oracle.com/technology/oramag/code/tips2005/011705.html

-- Jason
"It's Just Ones and Zeros

 

[bookouri]

Thanks that does simplify it. Its a lot easier to read and it handles both sides of the decimal. Now I just have to figure out how to handle a no decimal situation.

 

[SantaMufasa]

Bookouri,

Since there are inherent limitations using the "JSP" technique, I set about, years ago, while working at Oracle to build an all-purpose "Number to Words" and "Numbers to Dollars and Cents" function. Steven Feuerstein and Bill Pribyl liked the routine enough to include it as an example in their book, Oracle PL/SQL Programming. Here are examples of using my two packaged functions:

select DH_UTIL.SPELL(123456783923742938483478234.21344848484) spell from dual;

SPELL
--------------------------------------------------
One Hundred Twenty-Three Septillion Four Hundred
Fifty-Six Sextillion Seven Hundred Eighty-Three
Quintillion Nine Hundred Twenty-Three Quadrillion
Seven Hundred Forty-Two Trillion Nine Hundred
Thirty-Eight Billion Four Hundred Eighty-Three
Million Four Hundred Seventy-Eight Thousand Two
Hundred Thirty-Four and Twenty-One Billion Three
Hundred Forty-Four Million Eight Hundred
Forty-Eight Thousand Four Hundred Eighty-Four /
Hundred Billionths

Here is an example of the "Check Protection" routine that spells out dollars and cents of people's pay checks:

select 'Pay to the order of: '||
  rpad(ltrim(first_name||' '||last_name||' '),22,'*')
  ||'  '||lpad(rtrim(' '||ltrim(nvl(
  to_char(salary,'$99,999,990.00'),'Null Amount')
       )),16,'*')||chr(10)||
  rpad('** '||
  dh_util.check_protect(SALARY)||' ',56,'*') " "
from s_emp
where rownum <= 3;

Pay to the order of: Carmen Velasquez *****  ****** $2,698.25
** Two Thousand Six Hundred Ninety-Eight Dollars and Twenty-Five Cents

Pay to the order of: LaDoris Ngao *********  ****** $1,564.99
** One Thousand Five Hundred Sixty-Four Dollars and Ninety-Nine Cents

Pay to the order of: Midori Nagayama ******  ****** $1,511.00
** One Thousand Five Hundred Eleven Dollars and Zero Cents

Here is the code to my "DH_UTIL" Package that contains both the SPELL function and the CHECK_PROTECT function.

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor "Dasages, LLC" makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave@dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM NAME: SPELCHEK.SQL - PL/SQL code to create a package (DH_UTIL)
REM       providing 1) Number spelling and 2) Bank Draft/Checking
REM       protection via spelled amounts.
REM
REM AUTHOR: Dave Hunt
REM         Co-principal, Dasages, LLC
REM         1-801-733-5333
REM
REM **************************************************************
REM Maintenance History:
REM
REM 24-APR-96: Original Code
REM 03-MAR-96: Enhanced to handle 1) negative numbers and 2) zero
REM 16-JAN-97: Enhanced to translate non-integer decimal numbers
REM 20-JAN-97: Enhanced to handle numbers in the range:
REM            (-10 ** 100)+1 to (10 ** 100) -1 with precision to
REM            40 digits. Up to 40 digits behind the decimal point
REM            can be spelled.
REM 25-JAN-97: Added additional documentation and remarks.
REM **************************************************************
REM This package contains two GLOBAL functions:
REM   1) DH_UTIL.SPELL: Translates a number into English words.
REM      [Note: This version contains "American" (vs. "British")
REM             numeric-magnitude wordings: 
REM
REM                         Number American     British 
REM      ------------------------- -----------  -----------------
REM                  1,000,000,000 Billion      Milliard
REM              1,000,000,000,000 Trillion     Billion
REM          1,000,000,000,000,000 Quadrillion  Thousand Billion
REM      1,000,000,000,000,000,000 Quintillion  Trillion
REM
REM      For British system spellings, modify the table as needed
REM      at the end of the Package Body.]
REM 
REM **************************************************************
REM      Function 1 Usage: "DH_UTIL.SPELL(any-number)"
REM      SQL Example:
REM         SELECT last_name,
REM            salary, DH_UTIL.SPELL(salary) Worded
REM         FROM s_emp;"
REM
REM         LAST_NAME    SALARY WORDED
REM         ------------ ------ --------------------------------
REM         Velasquez      2500 Two Thousand Five Hundred
REM         Ngao           1450 One Thousand Four Hundred Fifty
REM         Nagayama       1400 One Thousand Four Hundred
REM
REM **************************************************************
REM      PL/SQL Example:
REM         BEGIN
REM            DBMS_OUTPUT.PUT_LINE
REM              (dh_util.spell(-123456789.123456789));
REM         END;
REM         /
REM         Negative One Hundred Twenty-Three Million Four 
REM         Hundred Fifty-Six Thousand Seven Hundred Eighty-Nine
REM         and One Hundred Twenty-Three Million Four 
REM         Hundred Fifty-Six Thousand Seven Hundred
REM         Eighty-Nine / Billionths
REM
REM **************************************************************
REM **************************************************************
REM   2) DH_UTIL.CHECK_PROTECT: Translates a number into spelled
REM         "Dollars & Cents".
REM      Function 2 Usage: "DH_UTIL.CHECK_PROTECT(any-number)"
REM **************************************************************
REM      SQL Example:
REM         select 'Pay to the order of: '||
REM           rpad(ltrim(first_name||' '||last_name||' '),22,'*')
REM           ||'  '||lpad(rtrim(' '||ltrim(nvl(
REM           to_char(salary,'$99,999,990.00'),'Null Amount')
REM                )),16,'*')||chr(10)||
REM           rpad('** '||
REM           dh_util.check_protect(SALARY)||' ',56,'*') " "
REM         from s_emp
REM         where rownum <= 3;
REM         
REM  Pay to the order of: Carmen Velasquez *****  ****** $2,500.00
REM  ** Two Thousand Five Hundred Dollars and Zero Cents *********
REM         
REM  Pay to the order of: LaDoris Ngao *********  ****** $1,450.00
REM  ** One Thousand Four Hundred Fifty Dollars and Zero Cents ***
REM       
REM  Pay to the order of: Midori Nagayama ******  ****** $1,400.00
REM  ** One Thousand Four Hundred Dollars and Zero Cents *********
REM
REM **************************************************************
REM      PL/SQL Example:
REM         begin
REM            dbms_output.put_line
REM               (dh_util.check_protect(123456789.56));
REM         end;
REM         /
REM      One Hundred Twenty-Three Million Four Hundred 
REM      Fifty-Six Thousand Seven Hundred Eighty-Nine Dollars
REM      and Fifty-Six Cents
REM
REM **************************************************************
REM DH_UTIL Package Specification
REM **************************************************************
create or replace package dh_util is
   function spell (x in number) return varchar2;
   function check_protect (x in number) return varchar2;
   pragma restrict_references(spell,WNDS);
   pragma restrict_references(check_protect,WNDS);
end;
/
REM **************************************************************
REM DH_UTIL Package Body
REM **************************************************************
create or replace package body dh_util is
  result   varchar2(2000);
  working_integer        number;
  working_decimal        varchar2(100);
  working_dec_mag        number;
  working_integer_spell  varchar2(2000);
  working_decimal_spell  varchar2(2000);
  working_fraction_spell varchar2(2000);
  type number_stencil is table of number
       index by binary_integer;
  type varchar2_stencil is table of varchar2(2000)
       index by binary_integer;
  denom varchar2_stencil;
  pad_factor number_stencil;
  hold varchar2_stencil;
--  **************************************************************
--  Packaged Global Function Definition: DH_UTIL.SPELL 
--  **************************************************************
function spell (x in number) return varchar2 is
--  **************************************************************
--  Local Function Specification: WORDING
--  **************************************************************
  function wording (x in number) return varchar2 is
  begin
     if x = 0 then
        return 'Zero';
     else
        return to_char(to_date(x,'j'),'Jsp'); -- Numbers-to-words
     end if;
  end wording;
--  **************************************************************
--  Local Function Specification: INTEGER_TRANSLATION
--  **************************************************************
  function integer_translation (working_x in number)
           return varchar2 is
     x_char varchar2(128);
     denoms_to_do number;
     start_byte   number;
     pointer      binary_integer;
     interim_spelling varchar2(2000);
  begin
     if working_x is null then
        return 'Null';
     elsif working_x = 0 then
        return 'Zero';
     end if;
     x_char := abs(working_x);
     pointer := 3-mod(length(x_char),3);
     x_char := lpad(x_char,length(x_char)+pad_factor(pointer),'0');
     denoms_to_do := length(x_char)/3;
     result := null;
     for i in 1..denoms_to_do loop
         start_byte := ((i-1)*3)+1;
         interim_spelling := wording(substr(x_char,start_byte,3));
         pointer := (denoms_to_do+1)-i;
         if upper(interim_spelling) <> 'ZERO' then
            result := rtrim(ltrim(result||' '||interim_spelling||
               ' '||denom(pointer)));
         end if;
         hold(i) := result;
     end loop;
     return result;
  end integer_translation;
--  **************************************************************
--  Global Function SPELL Procedural Section 
--  **************************************************************
begin
  working_integer_spell := null;
  working_decimal_spell := null;
  working_fraction_spell := null;
  working_integer := trunc(x);
  if abs(x) > abs(working_integer) then
     working_decimal :=
       substr(rtrim(to_char(abs(x)-abs(working_integer),
       '.00000000000000000000000000000000000000000'),
       '0'),3);
  else     
     working_decimal := null;
     working_dec_mag := null;
  end if; 
  working_integer_spell := integer_translation(working_integer);
  if working_decimal is not null then
     working_dec_mag := 10 ** length(working_decimal);
     working_decimal_spell := 
        ' and '||integer_translation(working_decimal);
     working_fraction_spell :=
        integer_translation(working_dec_mag)||'th';
     if working_decimal > 1 then
        working_fraction_spell := working_fraction_spell||'s';
     end if;
     if upper(substr(working_fraction_spell,1,3))='ONE' then
        working_fraction_spell := substr(working_fraction_spell,5);
     end if;
     working_fraction_spell := ' / '||working_fraction_spell;
  end if;
  if working_integer = 0 and working_decimal_spell is not null then
     result := substr(working_decimal_spell,5)||
        working_fraction_spell;
  else
     result := working_integer_spell||
        working_decimal_spell||working_fraction_spell;
  end if;
  if x < 0 then
     result := 'Negative '||result;
  end if;
  result := replace(result,'  ',' ');
  return result;
end spell;
--  **************************************************************
--  End of Global Function: SPELL
--  **************************************************************
--  **************************************************************
--  Global Function Specification: CHECK_PROTECT
--  **************************************************************
function check_protect (x in number) return varchar2 is
   hold_dollar number;
   hold_cents  number;
   function check_for_single (y in number, currency in varchar2)
      return varchar2 is
   begin
      if y = 1 then
         return 'One '||currency;
      else
         return spell(y) ||' '||currency||'s';
      end if;
   end;
begin
   if x is null then
      return 'Non Negotiable';
   end if;
   hold_dollar := trunc(x);
   hold_cents  := (abs(x) - trunc(abs(x)))*100;
   return check_for_single(hold_dollar,'Dollar')||' and '||
          check_for_single(hold_cents,'Cent');
end check_protect;
--  **************************************************************
--  "First-time-only" Package Initialization activities
--  **************************************************************
begin
   pad_factor(1) := 1;
   pad_factor(2) := 2;
   pad_factor(3) := 0;
   denom(1) := null;
   denom(2) := 'Thousand';
   denom(3) := 'Million';
   denom(4) := 'Billion';
   denom(5) := 'Trillion';
   denom(6) := 'Quadrillion';
   denom(7) := 'Quintillion';
   denom(8) := 'Sextillion';
   denom(9) := 'Septillion';
   denom(10) := 'Octillion';
   denom(11) := 'Nonillion';
   denom(12) := 'Decillion';
   denom(13) := 'Undecillion';
   denom(14) := 'Duodecillion';
   denom(15) := 'Tredecillion';
   denom(16) := 'Quattuordecillion';
   denom(17) := 'Quindecillion';
   denom(18) := 'Sexdecillion';
   denom(19) := 'Septendecillion';
   denom(20) := 'Octodecillion';
   denom(21) := 'Novemdecillion';
   denom(22) := 'Vigintillion';
   denom(23) := 'Unvigintillion';
   denom(24) := 'Duovigintillion';
   denom(25) := 'Trevigintillion';
   denom(26) := 'Quattuorvigintillion';
   denom(27) := 'Quinvigintillion';
   denom(28) := 'Sexvigintillion';
   denom(29) := 'Septenvigintillion';
   denom(30) := 'Octovigintillion';
   denom(31) := 'Novemvigintillion';
   denom(32) := 'Tregintillion';
   denom(33) := 'Untregintillion';
   denom(34) := 'Duotregintillion';
end dh_util;
--  **************************************************************
--  End of Global Function: SPELL
--  **************************************************************
/

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[bookouri]

Wow, thanks. Its going to take me a while to go through this one

 

[SantaMufasa]

Its going to take me a while to go through this one...

The nice thing about these two functions (as with Oracle built-in functions) is that you don't need to understand the internal workings to be able to use them effectively. You simply pass a single numeric value to each invocation, and, voila...you get your expected results. Enjoy.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ChrisHunt]

Whilst not as extensive as Santa's offering, you might like to look at my numbers-to-words package at

http://www.extraconnections.co.uk/code/cheque

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[bookouri]

Thanks, Ill take a look at that one too..

 

[KenCunningham]

There seems to be a distinct lack of stars on this thread, despite the excellent advice on offer?

Some days are diamonds, some days are rocks - make sure most are the former.

 

[bookouri]

You are absolutely correct.. i was getting there.. just slow as usual