| TO_CHAR [message #38049] |
Fri, 15 March 2002 03:34  |
Cyrille PETIT
Messages: 5
Registered: March 2002
|
Junior Member |
|
|
I need to convert date in caracters, I use the TO_CHAR
"Note that numbers spelled using the TO_CHAR function with NLS_DATE_LANGUAGE parameter
SELECT TO_CHAR(TO_DATE('12-Fév'),'Day: ddspth Month','NLS_DATE_LANGUAGE = FRENCH')
FROM DUAL;
But the function return
Mercredi: twenty-seventh Février"
How can I translate the number un french.
I read in the Oracle HELP that number returned by TO_CHAR function are always in english... Hos can i translate these number in French ?
TIA
Cyrille PETIT
|
|
|
|
| Re: TO_CHAR [message #38053 is a reply to message #38049] |
Fri, 15 March 2002 07:05  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
hi,
please find the code to convert code to the french
there is to array called
v_spell_array (for english language)
p_spell_array (for french language)
change the p_spell_array accordingly
u have to call the function like
a:=spellpack.spell(100);
-------------------------------
CREATE OR REPLACE PACKAGE SPELLPACK AS
--
TYPE SPELL_ARRAY IS TABLE OF VARCHAR2(100)
INDEX BY BINARY_INTEGER;
--
v_spell_array SPELL_ARRAY;
p_spell_array SPELL_ARRAY;
--
FUNCTION SPELL(v_num IN NUMBER) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(spell,WNDS);
--
END;
/
CREATE OR REPLACE PACKAGE BODY SPELLPACK AS
--------------------------------------------------------------------------
FUNCTION SPELL(v_num IN NUMBER) RETURN VARCHAR2 AS
v_word_out VARCHAR2(200):=NULL;
BEGIN
SELECT decode( sign( v_num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(v_num) ), +1, to_char( to_date( abs(v_num),'J'),'Jsp') )
into v_word_out from dual;
FOR i IN 0..v_spell_array.count-1
LOOP
v_word_out:=REPLACE(upper(v_word_out),upper(v_spell_array(i)),UPPER(p_spell_array(i)));
END LOOP;
RETURN v_word_out;
END;
--------------------------------------------------------------------------
BEGIN
-------------------------
v_spell_array(0):='zero';
v_spell_array(1):='one';
v_spell_array(2):='two';
v_spell_array(3):='three';
v_spell_array(4):='four';
v_spell_array(5):='five';
v_spell_array(6):='six';
v_spell_array(7):='seven';
v_spell_array(8):='eight';
v_spell_array(9):='nine';
v_spell_array(10):='ten';
v_spell_array(11):='eleven';
v_spell_array(12):='tweleve';
v_spell_array(13):='thirteen';
v_spell_array(14):='fourteen';
v_spell_array(15):='fifteen';
v_spell_array(16):='sixteen';
v_spell_array(17):='seventeen';
v_spell_array(18):='eighteen';
v_spell_array(19):='ninteen';
v_spell_array(20):='twenty';
v_spell_array(21):='thirty';
v_spell_array(22):='forty';
v_spell_array(23):='fifty';
v_spell_array(24):='sixty';
v_spell_array(25):='seventy';
v_spell_array(26):='eighty';
v_spell_array(27):='ninty';
v_spell_array(28):='hundred';
v_spell_array(29):='thousand';
v_spell_array(30):='million';
----------------------------------
p_spell_array(0):='zero**';
p_spell_array(1):='one**';
p_spell_array(2):='two**';
p_spell_array(3):='three**';
p_spell_array(4):='four**';
p_spell_array(5):='five**';
p_spell_array(6):='six**';
p_spell_array(7):='seven**';
p_spell_array(8):='eight**';
p_spell_array(9):='nine**';
p_spell_array(10):='ten**';
p_spell_array(11):='eleven**';
p_spell_array(12):='tweleve**';
p_spell_array(13):='thirteen**';
p_spell_array(14):='fourteen**';
p_spell_array(15):='fifteen**';
p_spell_array(16):='sixteen**';
p_spell_array(17):='seventeen**';
p_spell_array(18):='eighteen**';
p_spell_array(19):='ninteen**';
p_spell_array(20):='twenty**';
p_spell_array(21):='thirty**';
p_spell_array(22):='forty**';
p_spell_array(23):='fifty**';
p_spell_array(24):='sixty**';
p_spell_array(25):='seventy**';
p_spell_array(26):='eighty**';
p_spell_array(27):='ninty**';
p_spell_array(28):='hundred**';
p_spell_array(29):='thousand**';
p_spell_array(30):='million**';
END;
|
|
|
|
| Re: TO_CHAR [message #38061 is a reply to message #38049] |
Sun, 17 March 2002 23:04 |
Cyrille PETIT
Messages: 5
Registered: March 2002
|
Junior Member |
|
|
Thanks for the response,
I made a test with the package you send to me, i made the translation as :
CREATE OR REPLACE PACKAGE BODY SPELLPACK AS
--------------------------------------------------------------------------
FUNCTION SPELL(v_num IN NUMBER) RETURN VARCHAR2
IS
v_word_out VARCHAR2(200):=NULL;
BEGIN
SELECT decode( sign( v_num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(v_num) ), +1, to_char( to_date( abs(v_num),'J'),'Jsp') )
into v_word_out from dual;
FOR i IN 0..v_spell_array.count-1
LOOP
v_word_out:=REPLACE(upper(v_word_out),upper(v_spell_array(i)),UPPER(p_spell_array(i)));
END LOOP;
RETURN v_word_out;
END;
--------------------------------------------------------------------------
BEGIN
-------------------------
v_spell_array(0):='zero';
v_spell_array(1):='one';
v_spell_array(2):='two';
v_spell_array(3):='three';
v_spell_array(4):='four';
v_spell_array(5):='five';
v_spell_array(6):='six';
v_spell_array(7):='seven';
v_spell_array(8):='eight';
v_spell_array(9):='nine';
v_spell_array(10):='ten';
v_spell_array(11):='eleven';
v_spell_array(12):='tweleve';
v_spell_array(13):='thirteen';
v_spell_array(14):='fourteen';
v_spell_array(15):='fifteen';
v_spell_array(16):='sixteen';
v_spell_array(17):='seventeen';
v_spell_array(18):='eighteen';
v_spell_array(19):='ninteen';
v_spell_array(20):='twenty';
v_spell_array(21):='thirty';
v_spell_array(22):='forty';
v_spell_array(23):='fifty';
v_spell_array(24):='sixty';
v_spell_array(25):='seventy';
v_spell_array(26):='eighty';
v_spell_array(27):='ninety';
v_spell_array(28):='hundred';
v_spell_array(29):='thousand';
v_spell_array(30):='million';
----------------------------------
p_spell_array(0):='zero';
p_spell_array(1):='un';
p_spell_array(2):='deux';
p_spell_array(3):='trois';
p_spell_array(4):='quatre';
p_spell_array(5):='cinq';
p_spell_array(6):='six';
p_spell_array(7):='sept';
p_spell_array(8):='huit';
p_spell_array(9):='neuf';
p_spell_array(10):='dix';
p_spell_array(11):='onze';
p_spell_array(12):='douze';
p_spell_array(13):='treize';
p_spell_array(14):='quatorze';
p_spell_array(15):='quinze';
p_spell_array(16):='seize';
p_spell_array(17):='dix-sept';
p_spell_array(18):='dix-huit';
p_spell_array(19):='dix-neuf';
p_spell_array(20):='vingt';
p_spell_array(21):='trente';
p_spell_array(22):='quarante';
p_spell_array(23):='cinquante';
p_spell_array(24):='soixante';
p_spell_array(25):='soixante-dix';
p_spell_array(26):='quatre-vingt';
p_spell_array(27):='quatre-vingt-dix';
p_spell_array(28):='cent';
p_spell_array(29):='mille';
p_spell_array(30):='million';
END;
/
I've got a problem with for example the number
4382 =>
FOUR THOUSAND THREE HUNDRED EIGHTY-TWO
QUATRE MILLE TROIS CENT HUITY-DEUX
The function seems to replace EIGHTY by HUIT instead of QUATRE-VINGT ? So I obtain HUITy...
Have you a solution ?
TIA
Cyrille PETIT
|
|
|
|
| Re: TO_CHAR [message #38071 is a reply to message #38049] |
Mon, 18 March 2002 02:31 |
Cyrille PETIT
Messages: 5
Registered: March 2002
|
Junior Member |
|
|
Thanks for the rapidity !
The function seems to work but there are problem in case like :
396 =>
give : TROIS CENT QUATRE-VINGT-DIX-SIX
instead of : TROIS CENTE QUATRE VINGT SEIZE
4319 =>
give QUATRE MILLE TROIS CENT NEUFTEEN
instead of QUATRE MILLE TROIS CENT DIX-NEUF
5374 =>
give CINQ MILLE TROIS CENT SOIXANTE-DIX-QUATRE
instead of CINQ MILLE TROIS CENT SOIXANTE-QUATORZE
4192 =>
give QUATRE MILLE UN CENT QUATRE-VINGT-DIX-DEUX
instead of QUATRE MILLE UN CENT QUATRE-VINGT-DOUZE
With the table define as
v_spell_array(0):='zero';
v_spell_array(1):='one';
v_spell_array(2):='two';
v_spell_array(3):='three';
v_spell_array(4):='four';
v_spell_array(5):='five';
v_spell_array(6):='six';
v_spell_array(7):='seven';
v_spell_array(8):='eight';
v_spell_array(9):='nine';
v_spell_array(10):='ten';
v_spell_array(11):='eleven';
v_spell_array(12):='tweleve';
v_spell_array(13):='thirteen';
v_spell_array(14):='fourteen';
v_spell_array(15):='fifteen';
v_spell_array(16):='sixteen';
v_spell_array(17):='seventeen';
v_spell_array(18):='eighteen';
v_spell_array(19):='ninteen';
v_spell_array(20):='twenty';
v_spell_array(21):='thirty';
v_spell_array(22):='forty';
v_spell_array(23):='fifty';
v_spell_array(24):='sixty';
v_spell_array(25):='seventy';
v_spell_array(26):='eighty';
v_spell_array(27):='ninety';
v_spell_array(28):='hundred';
v_spell_array(29):='thousand';
v_spell_array(30):='million';
----------------------------------
p_spell_array(0):='zero';
p_spell_array(1):='un';
p_spell_array(2):='deux';
p_spell_array(3):='trois';
p_spell_array(4):='quatre';
p_spell_array(5):='cinq';
p_spell_array(6):='six';
p_spell_array(7):='sept';
p_spell_array(8):='huit';
p_spell_array(9):='neuf';
p_spell_array(10):='dix';
p_spell_array(11):='onze';
p_spell_array(12):='douze';
p_spell_array(13):='treize';
p_spell_array(14):='quatorze';
p_spell_array(15):='quinze';
p_spell_array(16):='seize';
p_spell_array(17):='dix-sept';
p_spell_array(18):='dix-huit';
p_spell_array(19):='dix-neuf';
p_spell_array(20):='vingt';
p_spell_array(21):='trente';
p_spell_array(22):='quarante';
p_spell_array(23):='cinquante';
p_spell_array(24):='soixante';
p_spell_array(25):='soixante-dix';
p_spell_array(26):='quatre-vingt';
p_spell_array(27):='quatre-vingt-dix';
p_spell_array(28):='cent';
p_spell_array(29):='mille';
p_spell_array(30):='million';
TIA
Cyrille PETIT
|
|
|
|
| Re: TO_CHAR [message #38073 is a reply to message #38049] |
Mon, 18 March 2002 03:33 |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
it seems ur table is wrong, change the array.i ran it here it seems rigth according to table data.
the whole idea is to replace the word in english to the corresponding language.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
