For counting out loud

select to_char(to_date( :num, 'J'), 'JSP') from dual;

The query above will return a spelled out number related to the supplied numeric parameter :num. You can play around with ‘Jsp’ / ‘jsp’ for different capitalization effects. When you’re at it, add a ‘TH’ format element to have words like ‘fourth’, ‘second’ etc. for your query result.

The to_date(..) part will convert the given number to a date value as a consequence of applying the “Julian day” datetime format element defined by the ‘J’ value for the second parameter of to_date. Subsequently, the to_char function will convert this date value back to a Julian number — if it weren’t told to also ‘SP’ell it out.

For me, however, there are two major concerns:

1. The accepted range of numbers is –somewhat– limited, i.e. from 1 to 5373484. Well, ok, I could live with that.
2. No matter how hard I try, like adding a third parameter to to_char defining the desired dutch language:

   select to_char(to_date( :num, 'J'), 'JSP','nls_date_language=dutch') from dual;

   the result value is always in English. That’s not a bug: it’s a documented operational note on this date format element suffix.

Of course the requirement for a translation to the Dutch langue is explained by my Dutch citizen status. The above led me to define the requirement definition below.

Requirement

Supply an API for conversion of a numeric (positive integer including nul) value to the spelled number, in the Dutch language.

Solution

A database package dutch_number_spelling has been implemented. It offers just one function, named to_char_sp, which accepts a numeric value as input and returns a (Dutch) spelled number as output.

A second (defaulted) parameter is supplied to have some control on a preference having to do with conversion of a specific range of values, which can either be phrased like a multiple of hundreds followed by the remainder ( the default ), or more puristic but less common, as a multiple of thousands followed by the remainder. The package can be found at the end of this blog.

Examples

SQL> select num, dutch_number_spelling.to_char_sp(num) spelled_num
 2 from ( select level num from dual connect by level <= 42 );

NUM SPELLED_NUM 
---------- ------------------------------
 1 één 
 2 twee 
 3 drie 
 4 vier 
 5 vijf 
 6 zes 
 7 zeven 
 8 acht 
 9 negen 
 10 tien 
 11 elf 
 12 twaalf 
 13 dertien 
 14 veertien 
 15 vijftien 
 16 zestien 
 17 zeventien 
 18 achttien 
 19 negentien 
 20 twintig 
 21 eenentwintig 
 22 tweeëntwintig 
 23 drieëntwintig 
 24 vierentwintig 
 25 vijfentwintig 
 26 zesentwintig 
 27 zevenentwintig 
 28 achtentwintig 
 29 negenentwintig 
 30 dertig 
 31 eenendertig 
 32 tweeëndertig 
 33 drieëndertig 
 34 vierendertig 
 35 vijfendertig 
 36 zesendertig 
 37 zevenendertig 
 38 achtendertig 
 39 negenendertig 
 40 veertig 
 41 eenenveertig 
 42 tweeënveertig

42 rows selected.

Well, and some huge numbers too:

exec dbms_output.put_line(dutch_number_spelling.to_char_sp(1969));
exec dbms_output.put_line(dutch_number_spelling.to_char_sp(1969,'THOUSANDS'));
exec dbms_output.put_line(dutch_number_spelling.to_char_sp(1e9+1));
exec dbms_output.put_line(dutch_number_spelling.to_char_sp(1e38-1));

negentienhonderdnegenenzestig

duizend negenhonderdnegenenzestig

één miljard één

negenennegentig sextiljoen negenhonderdnegenennegentig

quintiljard negenhonderdnegenennegentig quintiljoen

negenhonderdnegenennegentig quadriljard

Code

create or replace package dutch_number_spelling
is
 /*
 getallen uitschrijven volgens Genootschap Onze Taal
 d.d. 25 maart 2018
 https://onzetaal.nl/taaladvies/getallen-uitschrijven
 
 Hoe schrijf je getallen voluit, bijvoorbeeld 108, 2013, 2577 en 17.053.980?

Hele getallen in woorden worden aan elkaar geschreven, met de volgende uitzonderingen:
 - Na duizend komt een spatie.
 - Woorden als miljoen en miljard staan los.
 
 Voorbeelden:
 - 108: honderdacht
 - 678: zeshonderdachtenzeventig
 - 2016: tweeduizend zestien
 - 2577: tweeduizend vijfhonderdzevenenzeventig / vijfentwintighonderdzevenenzeventig
 - 17.053.980: zeventien miljoen drieënvijftigduizend negenhonderdtachtig

Wie aan het getal het woord en wil toevoegen, schrijft het aaneen na honderd en als apart woord na duizend, miljoen en 
 dergelijke:
 -108: honderdenacht
 -678: zeshonderdenachtenzeventig
 -2016: tweeduizend en zestien
 -2577: tweeduizend en vijfhonderdzevenenzeventig / vijfentwintighonderdenzevenenzeventig
 
 Namen van grote getallen: http://home.kpn.nl/vanadovv/Bignum.html

Who When What
 Rob van den Berg March 2018 Creation v1.1

constants
 preference_hundreds denotes a spelling preference mentioning a multiple of hundreds
 example 'vijfentwintighonderdzevenenzeventig'
 this is the default
 preference_hundreds denotes a spelling preference mentioning a multiple of thousands
 example 'tweeduizend vijfhonderdzevenenzeventig'

parameters
 num positive integer including zero to be spelled out
 accepted range is between 0 and 1e38-1 ( i.e. 39 nine's )
 spelling_preference
 defaulted to dutch_number_spelling.preference_hundreds
 modifies the spelling preference when a choice between mentioning hundreds or thousands is applicable
 
 exceptions
 e_preference_not_exists is raised when a value different from preference_hundreds and preference_thousands is passed to spelling_preference
 e_spelling_not_implemented is raised when the input is out of bounds, or not a natural number
 
*/

--constants
 preference_hundreds constant varchar2(8) default 'HUNDREDS';
 preference_thousands constant varchar2(9) default 'THOUSANDS';

--exceptions
 e_spelling_not_implemented exception;
 e_preference_not_exists exception;
 
 --functions
 function to_char_sp
 ( num in number
 , spelling_preference in varchar2 default preference_hundreds
 ) 
 return varchar2;
end dutch_number_spelling;
/

create or replace package body dutch_number_spelling
is
 c_spelling_0 constant varchar2(25) default 'nul';
 c_spelling_1 constant varchar2(25) default 'één';
 --holds spelled out numbers within the range of 1 to 14
 c_spelling_1_to_14
 constant dbmsoutput_linesarray 
 default dbmsoutput_linesarray 
 ( 'een'
 , 'twee'
 , 'drie'
 , 'vier'
 , 'vijf'
 , 'zes'
 , 'zeven'
 , 'acht'
 , 'negen'
 , 'tien'
 , 'elf'
 , 'twaalf'
 , 'dertien'
 , 'veertien'
 , 'vijftien'
 , 'zestien'
 , 'zeventien'
 , 'achttien'
 , 'negentien'
 );
 
 --holds spelled out numbers as a multiple of 10
 c_spelling_tens
 constant dbmsoutput_linesarray 
 default dbmsoutput_linesarray 
 ( 'tien'
 , 'twintig'
 , 'dertig'
 , 'veertig'
 , 'vijftig'
 , 'zestig'
 , 'zeventig'
 , 'tachtig'
 , 'negentig'
 );

c_spelling_100 constant varchar2(25) default 'honderd';
 c_spelling_1000 constant varchar2(25) default 'duizend';

c_spelling_big_numbers
 constant dbmsoutput_linesarray 
 default dbmsoutput_linesarray 
 ( 'miljoen' -- 1e6
 , 'biljoen' -- 1e12
 , 'triljoen' -- 1e18
 , 'quadriljoen' -- 1e24
 , 'quintiljoen' -- 1e30
 , 'sextiljoen' -- 1e36
 ); 
 
 function div
 ( p_number in number
 , p_divisor in number
 ) return number
 is
 begin
 return trunc(p_number/p_divisor);
 end div; 
 
 function add_en
 ( p_getal_sp in varchar)
 return varchar2
 is
 begin
 return 
 p_getal_sp||
 case substr(p_getal_sp,-1,1)
 when 'e' 
 then unistr('0ebn') -- 'en', e met trema
 else 'en'
 end; 
 end add_en;
 
 function to_digit_and_tens
 ( p_digit in number
 , p_tens in number
 ) return varchar2
 is
 begin
 return
 case
 when p_digit = 0
 then ''
 when p_tens = 1
 then c_spelling_1_to_14(p_digit)
 else add_en(c_spelling_1_to_14(p_digit))
 end
 ||c_spelling_tens(p_tens); 
 end to_digit_and_tens;
 
 function big_number_name
 ( p_exp_of_1000 in pls_integer
 )
 return varchar2
 is
 -- p_exp_of_1000 is expected to fall in the range of natural numbers equal or higher than 2
 -- and representing a value equal to power(1000,p_exp_of_1000)
 -- if 1eN is spelled one gazil"joen", 1e(N+3) is spelled one gazil"jard"
 l_big_number_name_prefix varchar2(200);
 begin
 l_big_number_name_prefix:=regexp_substr(c_spelling_big_numbers(div(p_exp_of_1000,2)),'(.*)joen$',1,1,'i',1);
 return 
 l_big_number_name_prefix||
 case mod(p_exp_of_1000,2)
 when 0
 then 'joen'
 when 1
 then 'jard'
 end; 
 end big_number_name;
 
 function to_char_sp
 ( p_number in number
 , p_reduce_singular_form in boolean
 , p_concatenated_spelling in boolean default false
 , p_count_hundreds in boolean default true
 , p_recursive in boolean default true
 ) 
 return varchar2
 is
 l_exp_of_1000 number;
 begin 
 case
 when p_number = 0
 then 
 return case when not p_recursive then c_spelling_0 end;
 when p_number = 1 and p_reduce_singular_form
 then
 return '';
 when p_number = 1 and not p_concatenated_spelling
 then
 return c_spelling_1;
 when p_number between 1 and 14
 then return c_spelling_1_to_14(p_number);
 when p_number between 15 and 99
 then return to_digit_and_tens
 ( p_digit => mod(p_number,10)
 , p_tens => div(p_number,10)
 );
 when p_number between 100 and 999
 then return to_char_sp
 ( p_number => div(p_number,100)
 , p_reduce_singular_form => true
 )||
 c_spelling_100||
 to_char_sp
 ( p_number => mod(p_number,100)
 , p_reduce_singular_form => false
 , p_concatenated_spelling => true
 );
 when p_number between 1000 and 9999 and p_count_hundreds
 and mod(div(p_number,100),10)!=0 -- preference does not apply to first century of each millenium
 then return to_char_sp
 ( p_number => div(p_number,100)
 , p_reduce_singular_form => true
 )||
 c_spelling_100||
 to_char_sp
 ( p_number => mod(p_number,100)
 , p_reduce_singular_form => false
 , p_concatenated_spelling => true
 );
 when p_number between 1000 and 999999
 then return to_char_sp
 ( p_number => div(p_number,1000)
 , p_reduce_singular_form => true
 )||
 c_spelling_1000||
 rtrim
 ( ' '||
 to_char_sp
 ( p_number => mod(p_number,1000)
 , p_reduce_singular_form => false
 )
 );
 else 
 l_exp_of_1000 := trunc ( log ( 1000 , p_number ));
 return to_char_sp
 ( p_number => div(p_number,power(1000, l_exp_of_1000))
 , p_reduce_singular_form => false
 )||' '||
 big_number_name(l_exp_of_1000)||
 rtrim
 ( ' '||
 to_char_sp
 ( p_number => mod(p_number,power(1000, l_exp_of_1000))
 , p_reduce_singular_form => false
 )
 );
 end case; 
 end to_char_sp;

procedure check_parameters
 ( p_number in number
 , p_spelling_preference in varchar2
 )
 is
 begin
 if p_number not between 0 and 1e39-1
 then
 raise e_spelling_not_implemented;
 end if; 
 if p_number <> trunc(p_number)
 then
 raise e_spelling_not_implemented;
 end if; 
 if p_spelling_preference not in ( preference_hundreds, preference_thousands)
 then
 raise e_preference_not_exists;
 end if; 
 end;
 
 function to_char_sp
 ( num in number
 , spelling_preference in varchar2 default preference_hundreds
 ) 
 return varchar2
 is
 begin
 check_parameters
 ( p_number => num
 , p_spelling_preference => spelling_preference
 );
 return 
 to_char_sp
 ( p_number => num
 , p_reduce_singular_form => false
 , p_count_hundreds => ( spelling_preference = preference_hundreds )
 , p_recursive => false
 );
 end to_char_sp;
end dutch_number_spelling;
/