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

The only way I can see is building a translate table and apply this translation to the string. For example, say you have these translations:

create table translate (eng, lat) as
(
select 'twenty',   'TWENTY' from dual union all
select 'thousand', 'THOUSAND' from dual union all
select 'eight',    'EIGHT' from dual union all
select 'four',     'FOUR' from dual union all
select 'hundred',  'HUNDRED' from dual union all
select 'thirty',   'THIRTY' from dual
) 

You could try:

with test(s) as (SELECT replace(TO_CHAR (TO_DATE (24834, 'j'), 'jsp'), '-', ' - ') FROM DUAL)
select replace ( listagg ( nvl(lat, tk), ' ') within group ( order by position), ' - ', '-')
from (
        SELECT regexp_substr(s, '[^ ]+', 1, level) as tk, level as position
          FROM test
        CONNECT BY instr(s, ' ', 1, level - 1) > 0
     ) tokens
left outer join translate tr
  on (tr.eng = tokens.tk)
order by position

which gives:

TWENTY-FOUR THOUSAND EIGHT HUNDRED THIRTY-FOUR

The inner query is a string splitter, to build a list of tokens from the string in English; the string is considered a list of tokens separated by spaces.

The external part simply joins this list of tokens to get the translation, paying attention to use NVL to handle the '-', that I consider a token.

The replace is useful first to wrap the '-' characters with spaces, so that they will be considered separated tokens buy the splitter, and then to remove the added spaces, restoring the initial structure of the string.

This way you don't need to hardcode your translations, but you can handle them as data.

Notice that this assumes that the only difference between English and Latvian in number spelling is about the single words, not in the structure ( I have no way to check if the assumption is true or not).

Answer 2

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.

Answer 3

like this ?

SELECT TO_CHAR (SYSDATE, 'DD/MON/YYYY',
'nls_date_language=''Traditional Chinese'' ') FROM DUAL

I'm not sure about Latvian language, you need to check manuals about supported languages