Oracle: convert numbers to words in other language than English

Question

I'm trying to convert numbers in words.

select to_char(to_date(:number,'j'),'jsp') from dual;



SELECT TO_CHAR (TO_DATE (24834, 'j'), 'jsp') FROM DUAL;
//Output: twenty-four thousand eight hundred thirty-four

But the problem is that I need convert numbers in other language than English. Maybe you have any ideas how to do this?

I need convert into Latvian language.

Answer 1

This is a cool trick (the jsp format to take a Julian and SPell it out). I found an Ask Tom article that gives more detail. But basically the jsp format will only work on English, but you can wrap it in a function and translate the english to another language.

For example, Tom's spell_number function is as follows:

create or replace 
 function spell_number( p_number in number ) 
 return varchar2 
 as 
 type myArray is table of varchar2(255); 
 l_str myArray := myArray( '', 
 ' thousand ', ' million ', 
 ' billion ', ' trillion ', 
 ' quadrillion ', ' quintillion ', 
 ' sextillion ', ' septillion ', 
 ' octillion ', ' nonillion ', 
 ' decillion ', ' undecillion ', 
 ' duodecillion ' ); 

 l_num varchar2(50) default trunc( p_number ); 
 l_return varchar2(4000); 
 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; 
 /

And a version for french (apparently) just uses spell_number with some french translations:

create or replace 
function spell_number_french( p_number in number ) 
return varchar2 
as 
begin 
return replace( replace( replace( replace( replace( 
replace( replace( replace( replace( replace( 
replace( replace( replace( replace( replace( 
replace( replace( replace( replace( replace( 
replace( replace( replace( replace( replace( 
replace( replace( replace( replace( replace( 
replace( replace( replace( replace( replace( 
replace( replace( replace( replace( replace( 
replace( 
lower( spell_number( p_number )) 
, 'duodecillion', 'bidecillion' ) 
, 'quintillion' , 'cintillion' ) 
, 'billion' , 'milliard' ) 
, 'thousand' , 'mille' ) 
, 'hundred' , 'cent' ) 
, 'ninety' , 'quatre-vingt-dix') 
, 'eighty' , 'quatre-vingt' ) 
, 'seventy' , 'soixante-dix' ) 
, 'sixty' , 'soixante' ) 
, 'fifty' , 'cinquante' ) 
, 'forty' , 'quarante' ) 
, 'thirty' , 'trente' ) 
, 'twenty' , 'vingt' ) 
, 'nineteen' , 'dix-neuf' ) 
, 'eighteen' , 'dix-huit' ) 
, 'seventeen' , 'dix-sept' ) 
, 'sixteen' , 'seize' ) 
, 'fifteen' , 'quinze' ) 
, 'fourteen' , 'quatorze' ) 
, 'thirteen' , 'treize' ) 
, 'twelve' , 'douze' ) 
, 'eleven' , 'onze' ) 
, 'ten' , 'dix' ) 
, 'nine' , 'neuf' ) 
, 'eight' , 'huit' ) 
, 'seven' , 'sept' ) 
, 'five' , 'cinq' ) 
, 'four' , 'quatre' ) 
, 'three' , 'trois' ) 
, 'two' , 'deux' ) 
, 'one' , 'un' ) 
, 'dix-six' , 'seize' ) 
, 'dix-cinq' , 'quinze' ) 
, 'dix-quatre' , 'quatorze' ) 
, 'dix-trois' , 'treize' ) 
, 'dix-deux' , 'douze' ) 
, 'dix-un' , 'onze' ) 
, '-un ' , '-une ' ) 
, 'un cent' , 'cent' ) 
, 'un mille' , 'mille' ) 
, 'une' , 'un' ); 
end spell_number_french; 

Do something similar to the language of your choice. And see that Ask Tom link for much more detailed discussion.