Konversi Nilai Rupiah Mysql

Posted: December 4, 2010 in Mysql
Tags: , ,

Berikut cara mengkonversi nilai ke Rupiah dari Mysql

/*** Konversi Nilai Rupiah ***/

DELIMITER $$

DROP FUNCTION IF EXISTS `tom`.`rupiah`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `rupiah`(
m decimal(18,2)) RETURNS varchar(254) CHARSET utf8
BEGIN
   /*------ WORD LIST (TELLING WORDS FROM 1 TO 100) ------*/
set @ie=concat("satu",SPACE(19),"dua",SPACE(20),"tiga",SPACE(19),"empat",SPACE(18),"lima",SPACE(19),
"enam",SPACE(19),"tujuh",SPACE(18),"delapan",SPACE(16),"sembilan",SPACE(15));
set @o=concat("sepuluh",SPACE(16),"sebelas",SPACE(16),"duabelas",SPACE(15),"tigabelas",SPACE(14),
"empatbelas",SPACE(13),"limabelas",SPACE(14),"enambelas",SPACE(14),"tujuhbelas",SPACE(13),
"delapanbelas",SPACE(11),"sembilanbelas",SPACE(10));
set @san=concat("duapuluh",SPACE(15),"duapuluh satu",SPACE(10),"duapuluh dua",SPACE(11),"duapuluh tiga",SPACE(10),
"duapuluh empat",SPACE(9),"duapuluh lima",SPACE(10),"duapuluh enam",SPACE(10),"duapuluh tujuh",SPACE(9),"duapuluh delapan",SPACE(7),"duapuluh sembilan",SPACE(6));
set @se=concat("tigapuluh",SPACE(14),"tigapuluh satu",SPACE(9),"tigapuluh dua",SPACE(10),"tigapuluh tiga",SPACE(9),
"tigapuluh empat",SPACE(8),"tigapuluh lima",SPACE(9),"tigapuluh enam",SPACE(9),"tigapuluh tujuh",SPACE(8),"tigapuluh delapan",SPACE(6),"tigapuluh sembilan",SPACE(5));
set @u=concat("empatpuluh",SPACE(13),"empatpuluh satu",SPACE(8),"empatpuluh dua",SPACE(9),"empatpuluh tiga",SPACE(8),"empatpuluh empat",SPACE(7),"empatpuluh lima",SPACE(8),"empatpuluh enam",SPACE(8),"empatpuluh tujuh",SPACE(7),"empatpuluh delapan",SPACE(5),"empatpuluh sembilan",SPACE(4));
set @liuk=concat("limapuluh",SPACE(14),"limapuluh satu",SPACE(9),"limapuluh dua",SPACE(10),"limapuluh tiga",SPACE(9),"limapuluh empat",SPACE(8),"limapuluh lima",SPACE(9),"limapuluh enam",SPACE(9),"limapuluh tujuh",SPACE(8),"limapuluh delapan",SPACE(6),"limapuluh sembilan",SPACE(5));
set @chi=concat("enampuluh",SPACE(14),"enampuluh satu",SPACE(9),"enampuluh dua",SPACE(10),"enampuluh tiga",SPACE(9),"enampuluh empat",SPACE(8),"enampuluh lima",SPACE(9),"enampuluh enam",SPACE(9),"enampuluh tujuh",SPACE(8),"enampuluh delapan",SPACE(6),"enampuluh sembilan",SPACE(5));
set @pat=concat("tujuhpuluh",SPACE(13),"tujuhpuluh satu",SPACE(8),"tujuhpuluh dua",SPACE(9),"tujuhpuluh tiga",SPACE(8),"tujuhpuluh empat",SPACE(7),"tujuhpuluh lima",SPACE(8),"tujuhpuluh enam",SPACE(8),"tujuhpuluh tujuh",SPACE(7),"tujuhpuluh delapan",SPACE(5),"tujuhpuluh sembilan",SPACE(4));
set @qiu=concat("delapanpuluh",SPACE(11),"delapanpuluh satu",SPACE(6),"delapanpuluh dua",SPACE(7),"delapanpuluh tiga",SPACE(6),"delapanpuluh empat",SPACE(5),"delapanpuluh lima",SPACE(6),"delapanpuluh enam",SPACE(6),"delapanpuluh tujuh",SPACE(5),"delapanpuluh delapan",SPACE(3),"delapanpuluh sembilan",SPACE(2));
set @sip=concat("sembilanpuluh",SPACE(10),"sembilanpuluh satu",SPACE(5),"sembilanpuluh dua",SPACE(6),"sembilanpuluh tiga",SPACE(5),"sembilanpuluh empat",SPACE(4),"sembilanpuluh lima",SPACE(5),"sembilanpuluh enam",SPACE(5),"sembilanpuluh tujuh",SPACE(4),"sembilanpuluh delapan",SPACE(2),"sembilanpuluh sembilan",SPACE(1),"seratus",SPACE(16));
set @tom=trim(concat(@ie,@o,@san,@se,@u,@liuk,@chi,@pat,@qiu,@sip));

 /*------------------ WORD STRUCTURE ---------------------*/
set @a=cast(m as unsigned);
set @b=cast(@a as char);
set @x=trim(@b);
set @len=length(@x);set @a=LENGTH(@x);set @aa=LENGTH(@x)-1;
set @aaa=LENGTH(@x)-2;set @aaaa=LENGTH(@x)-3;set @aaaaa=LENGTH(@x)-4;
set @aaaaaa=LENGTH(@x)-5;set @aaaaaaa=LENGTH(@x)-6;set @aaaaaaaa=LENGTH(@x)-7;set @aaaaaaaaa=LENGTH(@x)-8;set @aaaaaaaaaa=LENGTH(@x)-9;
set @aaaaaaaaaaa=LENGTH(@x)-10;set @aaaaaaaaaaaa=LENGTH(@x)-11;
set @aaaaaaaaaaaaa=LENGTH(@x)-12;set @aaaaaaaaaaaaaa=LENGTH(@x)-13;
set @csat=trim(substr(@x,@a,1));
set @cpul=trim(substr(@x,@aa,2));
set @crat=trim(substr(@x,@aaa,1));
set @crib=trim(substr(@x,@aaaa,1));
set @cpulrib=trim(substr(@x,@aaaaa,2));
set @cratrib=trim(substr(@x,@aaaaaa,1));
set @cjut=trim(substr(@x,@aaaaaaa,1));
set @cpuljut=trim(substr(@x,@aaaaaaaa,2));
set @cratjut=trim(substr(@x,@aaaaaaaaa,1));
set @cmil=trim(substr(@x,@aaaaaaaaaa,1));
set @cpulmil=trim(substr(@x,@aaaaaaaaaaa,2));
set @cratmil=trim(substr(@x,@aaaaaaaaaaaa,1));
set @ctrilyun=trim(substr(@x,@aaaaaaaaaaaaa,1));
set @sat=cast(@csat as unsigned);set @pul=cast(@cpul as unsigned);
set @rat=cast(@crat as unsigned);set @rib=cast(@crib as unsigned);
set @pulrib=cast(@cpulrib as unsigned);set @ratrib=cast(@cratrib as unsigned);set @jut=cast(@cjut as unsigned);set @puljut=cast(@cpuljut as unsigned);
set @ratjut=cast(@cratjut as unsigned);set @mil=cast(@cmil as unsigned);
set @pulmil=cast(@cpulmil as unsigned);set @ratmil=cast(@cratmil as unsigned);set @trilyun=cast(@ctrilyun as unsigned);

  /*--------------------- PARSER --------------------------*/
if @sat>0 then
set @tsat=trim(substr(@tom,(@sat-1)*23+1,23));
else
set @tsat="";
end if;

if @pul>0 then
set @tpul=trim(substr(@tom,(@pul-1)*23+1,23));
set @tsat="";
else
set @tpul="";
end if;

if @rat>1 and @len>2 then
set @trat=concat(trim(substr(@tom,((@rat)-1)*23+1,23)),"ratus");
elseif @rat=1 and @len>2 then
set @trat="seratus";
else
set @trat="";
end if;

if @rib>1 and @len>3 then
set @trib=concat(trim(substr(@tom,(@rib-1)*23+1,23)),"ribu");
elseif @rib=1 and @len>3 then
set @trib="seribu";
else
set @trib="";
end if;

if @pulrib>0 and @len>4 then
set @tpulrib=concat(trim(substr(@tom,(@pulrib-1)*23+1,23))," ribu");
set @trib="";
else
set @tpulrib="";
end if;

if @ratrib>1 and @pulrib>0 and @len>5 then
set @tratrib=concat(trim(substr(@tom,(@ratrib-1)*23+1,23)),"ratus");
elseif @ratrib=1 and @pulrib>0 and @len>5 then
set @tratrib="seratus";
elseif @ratrib>1 and @ratrib<>1 and @pulrib=0 and @rib=0 and @len>5 then
set @tratrib=concat(trim(substr(@tom,(@ratrib-1)*23+1,23)),"ratus ribu");
elseif @ratrib=1 and @pulrib=0 and @rib=0 and @len>5 then
set @tratrib="seratus ribu";
else
set @tratrib="";
end if;

if @jut>0 and @len>6 then
set @tjut=concat(trim(substr(@tom,(@jut-1)*23+1,23))," juta");
else
set @tjut="";
end if;

if @puljut>0 and @len>7 then
set @tpuljut=concat(trim(substr(@tom,(@puljut-1)*23+1,23))," juta");
set @tjut="";
else
set @tpuljut="";
end if;

if @ratjut>1 and @puljut>0 and @len>8  then
set @tratjut=concat(trim(substr(@tom,(@ratjut-1)*23+1,23)),"ratus");
elseif @ratjut=1 and @puljut>0 and @len>8 then
set @tratjut="seratus";
elseif @ratjut>1 and @puljut=0 and @jut=0 and @len> 8 then
set @tratjut=concat(trim(substr(@tom,(@ratjut-1)*23+1,23)),"ratus juta");
elseif @ratjut=1 and @puljut=0 and @jut=0 and @len> 8 then
set @tratjut="seratus juta";
else
set @tratjut="";
end if;

if @mil > 0 and @len>9 then
set @tmil=concat(trim(substr(@tom,((@mil)-1)*23+1,23))," milyar");
else
set @tmil="";
end if;

if @pulmil >0 and @len>10 then
set @tpulmil=concat(trim(substr(@tom,((@pulmil)-1)*23+1,23))," milyar");
set @tmil="";
else
set @tpulmil="";
end if;

if @ratmil>1 and @pulmil>0 and @len>11 then
set @tratmil=concat(trim(substr(@tom,((@ratmil)-1)*23+1,23)),"ratus");
elseif @ratmil=1 and @pulmil>0 and @len>11 then
set @tratmil="seratus";
elseif @ratmil>1 and @pulmil=0 and @mil=0 and @len>11 then
set @tratmil=concat(trim(substr(@tom,((@ratmil)-1)*23+1,23)),"ratus milyar");
elseif @ratmil=1 and @pulmil=0 and @mil=0 and @len>11 then
set @tratmil="seratus milyar";
else
set @tratmil="";
end if;

if @tril>0 and @len> 12 then
set @ttril=concat(trim(substr(@tom,((@trilyun)-1)*23+1,23))," trilyun");
else
set @ttril="";
end if;

set @fraction=format(m,2);set @cfraction=concat(@fraction);
set @lrfraction=right(@cfraction,2);set @lfraction=left(@lrfraction,1);set @rfraction=right(@lrfraction,1);
set @lf=cast(@lfraction as unsigned);set @rf=cast(@rfraction as unsigned);

if @lf>0 then
set @tlf=trim(substr(@tom,(@lf-1)*23+1,23));
else
set @tlf="nol";
end if;

if @rf>0 then
set @trf=trim(substr(@tom,(@rf-1)*23+1,23));
else
set @trf="nol";
end if;

set @comma=(select case when @lrfraction="00" then "" else "koma" end);

if @lrfraction="00" then
set @tlf="";
else
set @tlf=concat(space(1),@tlf,space(1));
end if;

if @lrfraction="00" then
set @trf="";
else
set @trf=concat(@trf,space(1));
end if;

set @myword=trim(concat(@ttril,space(1),@tratmil,@tpulmil,@tmil,space(1),
@tratjut,@tpuljut,@tjut,space(1),
@tratrib,@tpulrib,@trib,space(1),@trat,@tpul,@tsat,space(1),@comma,@tlf,@trf,"rupiah"));
return ucase(@myword);
    END$$

DELIMITER ;

/*  Caranya , ketikkan:  */
/*  select rupiah(95107391054.12);  */

Selamat Mencoba …:D

CMIIW…:D

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