Voici deux fonctions permettant de transformer un numérique en lettres.
Exemple d'appel:
SELECT translate_fr(to_word_en(4567891345678.23)) FROM dual;
Fonction : to_word_en CREATE OR REPLACE FUNCTION to_word_en(pn$nombre IN NUMBER)
RETURN VARCHAR2
AS
TYPE table_varchar IS TABLE OF VARCHAR2 (255);
lv$multiples table_varchar := table_varchar ('',
' thousand ',
' million ',
' billion ',
' trillion ',
' quadrillion ',
' quintillion ',
' sextillion ',
' septillion ',
' octillion ',
' nonillion ',
' decillion ',
' undecillion ',
' duodecillion ',
' tridecillion ',
' quaddecillion ',
' quindecillion ',
' sexdecillion ',
' septdecillion ',
' octdecillion ',
' nondecillion ',
' dedecillion '
);
lv$entier VARCHAR2(255) := TRUNC (TO_NUMBER (REPLACE (pn$nombre, ' ', '')));
lv$decimales VARCHAR2(255) := SUBSTR (pn$nombre - lv$entier, 2);
lv$mots_complets VARCHAR2(4000);
lv$entier_lettres VARCHAR2(4000);
li$nb_zero INTEGER;
BEGIN
IF NVL (lv$decimales, 0) != 0
THEN
FOR i IN 1 .. lv$multiples.COUNT
LOOP
EXIT WHEN lv$decimales IS NULL;
IF (SUBSTR (lv$decimales, LENGTH (lv$decimales) - 2, 3) <> 0)
THEN
lv$mots_complets :=
TO_CHAR (TO_DATE (SUBSTR (lv$decimales,
LENGTH (lv$decimales) - 2,
3
),
'j'
),
'jsp'
)
|| lv$multiples (i)
|| lv$mots_complets;
END IF;
lv$decimales := SUBSTR (lv$decimales, 1, LENGTH (lv$decimales) - 3);
END LOOP;
li$nb_zero := INSTR(TRANSLATE(SUBSTR (pn$nombre - lv$entier, 2),'123456789','x'),'x')-1;
IF li$nb_zero > 0 THEN
FOR i IN 1..li$nb_zero LOOP
lv$mots_complets := 'zero ' || lv$mots_complets;
END LOOP;
END IF;
lv$mots_complets := ' point ' || lv$mots_complets;
END IF;
IF NVL (lv$entier, 0) = 0
THEN
lv$mots_complets := 'zero' || lv$mots_complets;
ELSE
FOR i IN 1 .. lv$multiples.COUNT
LOOP
EXIT WHEN lv$entier IS NULL;
IF (SUBSTR (lv$entier, LENGTH (lv$entier) - 2, 3) <> 0)
THEN
lv$mots_complets :=
TO_CHAR (TO_DATE (SUBSTR (lv$entier, LENGTH (lv$entier) - 2,
3),
'j'
),
'jsp'
)
|| lv$multiples (i)
|| lv$mots_complets;
END IF;
lv$entier := SUBSTR (lv$entier, 1, LENGTH (lv$entier) - 3);
END LOOP;
END IF;
RETURN lv$mots_complets;
END to_word_en;
/
Fonction : translate_fr CREATE OR REPLACE FUNCTION translate_fr(pn$nombre_en IN VARCHAR2)
RETURN VARCHAR2
AS
lv$nombre_fr VARCHAR2(255);
BEGIN
lv$nombre_fr := 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( 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( 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(
pn$nombre_en
, 'million' , 'millions' )
, 'billion' , 'milliards' )
, 'trillion' , 'trillions' )
, 'quadrillion' , 'quadrillions' )
, 'quintillion' , 'cintillions' )
, 'sextillion' , 'sextillions' )
, 'septillion' , 'septillions' )
, 'octillion' , 'octillions' )
, 'nonillion' , 'nonillions' )
, 'decillion' , 'decillions' )
, 'undecillion' , 'undecillions' )
, 'duodecillion' , 'duodecillions' )
, 'tridecillion' , 'tridecillions' )
, 'quaddecillion' , 'quaddecillions' )
, 'quindecillion' , 'quindecillions' )
, 'sexdecillion' , 'sexdecillions' )
, 'septdecillion' , 'septdecillions' )
, 'octdecillion' , 'octdecillions' )
, 'nondecillion' , 'nondecillions' )
, 'dedecillion' , 'dedecillions' )
, 'thousand' , 'mille' )
, 'hundred' , 'cent' )
, 'ninety' , 'quatre-vingt-dix')
, 'eighty' , 'quatre-vingts' )
, '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' )
, 'une' , 'un' )
, 'soixante-onze' , 'soixante et onze')
, 'quatre-vingts-' , 'quatre-vingt-' )
, '-un' , ' et un' )
, 'quatre-vingt et un', 'quatre-vingt-un' )
, 'deux cent' , 'deux cents' )
, 'trois cent' , 'trois cents' )
, 'quatre cent' , 'quatre cents' )
, 'cinq cent' , 'cinq cents' )
, 'six cent' , 'six cents' )
, 'sept cent' , 'sept cents' )
, 'huit cent' , 'huit cents' )
, 'neuf cent' , 'neuf cents' )
, 'cents ' , 'cent ' )
, 'un millions' , 'un million' )
, 'un bidecillions' , 'un bidecillion' )
, 'un cintillions' , 'un cintillion' )
, 'un milliards' , 'un milliard' )
, 'un trillions' , 'un trillion' )
, 'un quadrillions' , 'un quadrillion' )
, 'un sextillions' , 'un sextillion' )
, 'un septillions' , 'un septillion' )
, 'un octillions' , 'un octillion' )
, 'un nonillions' , 'un nonillion' )
, 'un decillions' , 'un decillion' )
, 'un undecillions' , 'un undecillion' )
, 'un duodecillions' , 'un duodecillion' )
, 'un tridecillions' , 'un tridecillion' )
, 'un quaddecillions' , 'un quaddecillion' )
, 'un quindecillions' , 'un quindecillion' )
, 'un sexdecillions' , 'un sexdecillion' )
, 'un septdecillions' , 'un septdecillion' )
, 'un octdecillions' , 'un octdecillion' )
, 'un nondecillions' , 'un nondecillion' )
, 'un dedecillions' , 'un dedecillion' )
, '-un trillion' , '-un trillions' )
, '-un quadrillion' , '-un quadrillions' )
, '-un sextillion' , '-un sextillions' )
, '-un septillion' , '-un septillions' )
, '-un octillion' , '-un octillions' )
, '-un nonillion' , '-un nonillions' )
, '-un decillion' , '-un decillions' )
, '-un undecillion' , '-un undecillions' )
, '-un duodecillion' , '-un duodecillions' )
, '-un tridecillion' , '-un tridecillions' )
, '-un quaddecillion' , '-un quaddecillions')
, '-un quindecillion' , '-un quindecillions')
, '-un sexdecillion' , '-un sexdecillions' )
, '-un septdecillion' , '-un septdecillions')
, '-un octdecillion' , '-un octdecillions' )
, '-un nondecillion' , '-un nondecillions' )
, '-un dedecillion' , '-un dedecillions' )
, '-un million' , '-un millions' )
, '-un bidecillion' , '-un bidecillions')
, '-un cintillion' , '-un cintillions' )
, '-un milliard' , '-un milliards' )
, 'point' , 'virgule' )
,' ',' ');
IF SUBSTR(lv$nombre_fr,1,8)='un mille' THEN
lv$nombre_fr := SUBSTR(lv$nombre_fr,4);
END IF;
RETURN lv$nombre_fr;
END translate_fr;
/
| 
