PLSQL SUBSTR function ignore the trailing zero

Question

select TO_NUMBER (SUBSTR(10.31, INSTR (10.31, '.') + 1)) from dual

Above query returns 31 as the output. But below query returns 3 as the output.

select TO_NUMBER (SUBSTR(10.30, INSTR (10.30, '.') + 1)) from dual

How could I get the 30 as the output instead of the 3?

Answer 1

As it seems (from comments) that you are starting with a numeric value that you want to turn into words, you should begin by splitting it into dollars and cents.

If you really need to use substr etc, then you could start with a known format, such as to_char(amount,'fm9990.00'), so it will be a string with exactly two decimal places. However, if you have the numeric value it would be easier to convert it into the desired units using arithmetic functions. Whole dollars are trunc(amount) and cents are 100 * mod(amount,1).

Another issue is that the 'Jsp' date format approach can't handle zeroes. If you are using Oracle 12.2 or later there is a workaround using the default on conversion error clause:

create table demo
( amount number(6,2) );

insert into demo values (10.3);
insert into demo values (.25);
insert into demo values (25);

select amount
     , nvl(to_char(to_date(trunc(amount) default null on conversion error,'J'),'Jsp'),'Zero') as dollars
     , nvl(to_char(to_date(100 * mod(amount,1) default null on conversion error,'J'),'Jsp'),'Zero') as cents
from   demo;

  AMOUNT DOLLARS      CENTS
-------- ------------ -------------
   10.30 Ten          Thirty
   25.00 Twenty-Five  Zero
    0.25 Zero         Twenty-Five

In 12.1 you could get around it using an inline function (maybe not a bad idea even in later versions, to simplify the rest of the query):

with
     function to_words(num number) return varchar2 as
     begin
         return
             case num
                 when 0 then 'Zero'
                 else to_char(to_date(num,'J'),'Jsp')
             end;
     end; 
select amount
     , to_words(trunc(amount)) as dollars
     , to_words(100 * mod(amount,1)) as cents
from   demo;

For values greater than 5373484 (the Julian representation of date '9999-12-31'), you can use this from Ask Tom: Spell the number (converted here to a WITH clause, but you can create it as a standalone function):

with function spell_number
    ( p_number in number )
    return varchar2
as
    -- Tom Kyte, 2001:
    -- https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1407603857650
    l_num    varchar2(50) := trunc(p_number);
    l_return varchar2(4000);

    type myarray is table of varchar2(15);

    l_str myarray :=
        myarray
        ( ''
        , ' thousand '
        , ' million '
        , ' billion '
        , ' trillion '
        , ' quadrillion '
        , ' quintillion '
        , ' sextillion '
        , ' septillion '
        , ' octillion '
        , ' nonillion '
        , ' decillion '
        , ' undecillion '
        , ' duodecillion ');
begin
    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;

    return l_return;
end spell_number;
select amount
     , spell_number(trunc(amount)) as dollars
     , spell_number(100 * mod(amount,1)) as cents
from   demo
/