Edi Yanto (何 萬 新)

Spell Number (Function Terbilang)

Posted on: October 28, 2008


CREATE OR REPLACE PACKAGE spell_number_pkg AS
/*
Spell Number in Indonesia and English
*/
AMOUNT_EXCEEDS_LIMIT EXCEPTION;

function to_words_id(
p_amount number
) return varchar2;

function to_words_en(
p_number in number
) return varchar2;
END spell_number_pkg;

CREATE OR REPLACE PACKAGE BODY spell_number_pkg
IS
/*
Spell Number in Indonesia and English
*/
–*******************************************************************
–* PRIVATE MEMBERS
TYPE varchar2_tbl_t IS TABLE OF VARCHAR2 (30);

FUNCTION to_hundredth_words (p_amount NUMBER)
RETURN VARCHAR2
IS
l_translate_groups   varchar2_tbl_t
:= varchar2_tbl_t (‘SATU’,
‘DUA’,
‘TIGA’,
‘EMPAT’,
‘LIMA’,
‘ENAM’,
‘TUJUH’,
‘DELAPAN’,
‘SEMBILAN’
);
l_tenth_groups       varchar2_tbl_t
:= varchar2_tbl_t (”, ‘PULUH’, ‘RATUS’);
l_char               VARCHAR2 (3);
l_length             NUMBER;
l_number             NUMBER;
l_words              VARCHAR2 (2000);
BEGIN
l_char := TO_CHAR (p_amount);
l_length := LENGTH (l_char);

FOR i IN 1 .. l_length
LOOP
l_number := TO_NUMBER (SUBSTR (l_char, l_length + 1 – i, 1));

IF (l_number != 0)
THEN
l_words :=
l_translate_groups (l_number)
|| RTRIM (‘ ‘ || l_tenth_groups (i))
|| RTRIM (‘ ‘ || l_words);
END IF;
END LOOP;

IF (l_words IS NOT NULL)
THEN
l_words := REPLACE (l_words, ‘SATU RATUS’, ‘SERATUS’);
l_words := REPLACE (l_words, ‘SATU PULUH SATU’, ‘SEBELAS’);
l_words := REPLACE (l_words, ‘SATU PULUH’, ‘SEPULUH’);
l_words := REPLACE (l_words, ‘SEPULUH DUA’, ‘DUA BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH TIGA’, ‘TIGA BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH EMPAT’, ‘EMPAT BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH LIMA’, ‘LIMA BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH ENAM’, ‘ENAM BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH TUJUH’, ‘TUJUH BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH DELAPAN’, ‘DELAPAN BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH SEMBILAN’, ‘SEMBILAN BELAS’);
END IF;

RETURN l_words;
END to_hundredth_words;

–**********************************************************
–* PUBLIC MEMBERS
FUNCTION to_words_id (p_amount NUMBER)
RETURN VARCHAR2
IS
l_thousandth_groups   varchar2_tbl_t
:= varchar2_tbl_t (”, ‘RIBU’, ‘JUTA’, ‘MILIAR’, ‘TRILIUN’);
l_idx                 NUMBER          := 1;
l_amount              NUMBER;
l_fraction            NUMBER;
l_hundredth_words     VARCHAR2 (240);
l_words               VARCHAR2 (2000);
BEGIN
l_amount := TRUNC (ABS (p_amount));

IF (LENGTH (TO_CHAR (l_amount)) > 3 * l_thousandth_groups.COUNT)
THEN
RAISE amount_exceeds_limit;
END IF;

l_fraction := ABS (ROUND (p_amount, 2)) – l_amount;

LOOP
l_hundredth_words :=
to_hundredth_words (l_amount – TRUNC (l_amount, -3));

IF (l_hundredth_words IS NOT NULL)
THEN
l_words :=
l_hundredth_words
|| RTRIM (‘ ‘ || l_thousandth_groups (l_idx))
|| RTRIM (‘ ‘ || l_words);
END IF;

l_amount := TRUNC (l_amount, -3) / 1000;
–Updated by Edi Yanto on 10-Aug-2011
if l_idx=2 and l_words = ‘SATU RIBU’ then
l_words := ‘SERIBU’;
end if;
–End
EXIT WHEN l_amount = 0;
l_idx := l_idx + 1;
END LOOP;

IF (SUBSTR (l_words, 1, 9) = ‘SATU RIBU’)
THEN
l_words := ‘SERIBU’ || SUBSTR (l_words, 10);
END IF;

l_words := l_words;

IF (l_fraction != 0)
THEN
l_words :=
l_words || ‘ ‘ || to_hundredth_words (l_fraction * 100)
|| ‘ SEN’;
END IF;

RETURN l_words;
END to_words_id;

FUNCTION to_words_en (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);
l_fraction VARCHAR2 (2);
BEGIN
FOR i IN 1 .. l_str.COUNT
LOOP
EXIT WHEN l_num IS NULL;

IF (TO_NUMBER (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;

if instr(p_number,’.’,1) <> 0 then
l_fraction := substr(p_number,instr(p_number,’.’,1)+1,2);
l_return := l_return || ‘ DOLLARS ‘ || to_char(to_date(l_fraction,’J’),’JSP’) || ‘ CENTS’;
end if;

RETURN l_return;
END to_words_en;
END spell_number_pkg;
/

4 Responses to "Spell Number (Function Terbilang)"

Heya everyone, I’m a newb to this unique web, on the other hand I have recently been researching this for quite some time now, and the data is very much fantasti’c. Thanks heaps!

Gourmet Food

Like

Very great information thank you kindly
Kathy Miller

Like

Very great information thank you kindly
Thanks

Like

Thank you Edi,

nice posting, very helping…. ijin pake scriptnya ya gan…

Petrus.Samora

Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

October 2008
M T W T F S S
« Sep   Nov »
 12345
6789101112
13141516171819
20212223242526
2728293031  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: