Learn the Rules, Break The Rules, and Create the New Ones...

Hi... My name is Rizky Prihanto. You can call me RQ, or Rizky either. I am currently living on Bandung, Indonesia. Had a lot of works and research about Enterprise Information Systems (majoring on education and e-governments). I have bunch of interests (some friends call it 'freakz') about MySQL Opensource Database and now I am one of the administrator of MySQL Indonesia User Group - the opensource community initialized by Sun Microsystems Indonesia.

My Company PT Cinox Media Insani, Bandung, Indonesia. I work here since 2008 and I take responsibility as Chief of Software Architect. My job is about planning, imaginating, fantasy-ing, concepting, and build the infrastructure of the new information systems (or app engines) which going to be implemented.

This blog This is my blog that represent my current opinion, research and experiences about anything in Software Engineering. Written since 2007 (actually) and has been vaccum for a lot of while. And now I wanna ressurrect this blog (optimistically) from the long-long-hibernation with something fresh and new ideas -- still about MySQL, software engineering, development, and may be something managerial here.

About the tagline I've learned the statement above from some paper written by Kent Beck about Extreme Programming (XP) methodology -- some sort of practical software development methods which have no boundaries. That's very inspiring me a lot. I have written some article on this blog that tell my interpretation about that statement here.

My Another Blogs I have classifying my blogs into some sort of genre. The blog that you read here right now is my primary blog that tell you (majoring) about IT stuff. But if you wanna look another side of me, you can visit here, here, here,or here. Hope it'll be interesting for some of you.

Credits I would thanks to Blogger for this great blog platform. Skinpress who designed this Wordpress template (which is bloggerized by Free Blogger Templates). My appreciate is also going to you who give your generously time for visiting my blog.

Stored Function Terbilang di MySQL

Lagi iseng mode = on.

Karena bete gara2 ga bs nge-YouTube, barusan aku bikin stored function di MySQL yang akan mengconvert bilangan ke dalam 'terbilang' (entah siapa yg memulai mengistilahkan ini dengan nama 'terbilang' -- mgkn terinispirasi dari tulisan di kuitansi-kuitansi itu kali ye?). Dengan stored function utk mekanisme pembuat 'terbilang' ini, keuntungan2nya :

1. bisa di embedd di query kita tanpa perlu memprocessing ulang di front-end (sisi aplikasi).
2. ...sebenarnya cuman itu aja sih.., kan klo proses pengconvertan dilakukan di sisi
engine MySQL nya, artinya :

  • client ngga perlu sok-sok-an ngitung. klo develop di web sih ngga masalah, tp klo u bikin desktop,kan komputasi nya di sisi client. *walo ngga ngefek jg sih hare gene mana ada client yg mau makae kompie "bosok" berdaya komputasi rendah.
  • dengan di-embedd nya ke dalam query, manfaat nya, *tentu* mempermudah pengambilan data. mempermudah display ke grid, ato mungkin ke crystal report (seperti ulasan mas madhi dalam blog nya dulu). pokoke, mudah lah..

begini function nya : (silakan co-pas dan execute di konsole-konsole MySQL kesayangan anda)


CREATE DEFINER=`root`@`localhost` FUNCTION `xf_terbilang`(angka bigint) RETURNS varchar(5000) CHARSET utf8
BEGIN
DECLARE sString varchar(30);
DECLARE Bil1 varchar(255);
DECLARE Bil2 varchar(255);
DECLARE STot varchar(255);
DECLARE X int;
DECLARE Y int;
DECLARE Z int;
DECLARE Urai varchar(5000);
SET sString = CAST(angka as char);
SET Urai = '';
SET X = 0;
SET Y = 0;
WHILE X <>
SET X = X + 1;
SET sTot = MID(sString, X, 1);
SET Y = Y + CAST(sTot as UNSIGNED);
SET Z = LENGTH(sString) - X + 1;
CASE CAST(sTot as UNSIGNED)
WHEN 1 THEN
BEGIN
IF (Z = 1 OR Z = 7 OR Z = 10 OR Z = 13) THEN
SET Bil1 = 'SATU ';
ELSEIF (z = 4) THEN
IF (x = 1) THEN
SET Bil1 = 'SE';
ELSE
SET Bil1 = 'SATU';
END IF;
ELSEIF (Z = 2 OR Z = 5 OR Z = 8 OR Z = 11 OR Z = 14) THEN
SET X = X + 1;
SET sTot = MID(sString, X, 1);
SET Z = LENGTH(sString) - X + 1;
SET Bil2 = '';
CASE CAST(sTot AS UNSIGNED)
WHEN 0 THEN SET Bil1 = 'SEPULUH ';
WHEN 1 THEN SET Bil1 = 'SEBELAS ';
WHEN 2 THEN SET Bil1 = 'DUA BELAS ';
WHEN 3 THEN SET Bil1 = 'TIGA BELAS ';
WHEN 4 THEN SET Bil1 = 'EMPAT BELAS ';
WHEN 5 THEN SET Bil1 = 'LIMA BELAS ';
WHEN 6 THEN SET Bil1 = 'ENAM BELAS ';
WHEN 7 THEN SET Bil1 = 'TUJUH BELAS ';
WHEN 8 THEN SET Bil1 = 'DELAPAN BELAS ';
WHEN 9 THEN SET Bil1 = 'SEMBILAN BELAS ';
ELSE BEGIN END;
END CASE;
ELSE
SET Bil1 = 'SE';
END IF;
END;
WHEN 2 THEN SET Bil1 = 'DUA ';
WHEN 3 THEN SET Bil1 = 'TIGA ';
WHEN 4 THEN SET Bil1 = 'EMPAT ';
WHEN 5 THEN SET Bil1 = 'LIMA ';
WHEN 6 THEN SET Bil1 = 'ENAM ';
WHEN 7 THEN SET Bil1 = 'TUJUH ';
WHEN 8 THEN SET Bil1 = 'DELAPAN ';
WHEN 9 THEN SET Bil1 = 'SEMBILAN ';
ELSE SET Bil1 = '';
END CASE;
IF CAST(sTot as UNSIGNED) > 0 THEN
IF (Z = 2 OR Z = 5 OR Z = 8 OR Z = 11 OR Z = 14) THEN
SET Bil2 = 'PULUH ';
ELSEIF (Z = 3 OR Z = 6 OR Z = 9 OR Z = 12 OR Z = 15) THEN
SET Bil2 = 'RATUS ';
ELSE
SET Bil2 = '';
END IF;
ELSE
SET Bil2 = '';
END IF;
IF Y > 0 THEN
CASE Z
WHEN 4 THEN BEGIN SET Bil2 = CONCAT(Bil2, 'RIBU '); SET Y = 0; END;
WHEN 7 THEN BEGIN SET Bil2 = CONCAT(Bil2, 'JUTA '); SET Y = 0; END;
WHEN 10 THEN BEGIN SET Bil2 = CONCAT(Bil2, 'MILYAR '); SET Y = 0; END;
WHEN 13 THEN BEGIN SET Bil2 = CONCAT(Bil2, 'TRILYUN '); SET Y = 0; END;
ELSE BEGIN END;
END CASE;
END IF;
SET Urai = CONCAT(Urai, Bil1, Bil2);
END WHILE;
RETURN Urai;
END;


nah cara pemanggilannya, coba gini :

mysql> select xf_terbilang(512250300) as urai;
+-----------------------------------------------------------------+
| Urai |
+-----------------------------------------------------------------+
| LIMA RATUS DUA BELAS JUTA DUA RATUS LIMA PULUH RIBU TIGA RATUS |
+-----------------------------------------------------------------+
1 row in set

Query OK, 0 rows affected

Asyeek...
sukses!

eh bentar dulu...

setelah nyoba2, ternyata ada "limitasi" nya nie function. *mencoba berfikir obyektif nih. ndak selamanya kita musti narcis* hihihi...

ternyata : ini maksimal nilai yang VALID utk di-convert.

mysql> select xf_terbilang(999999999999999);
SEMBILAN RATUS SEMBILAN PULUH SEMBILAN TRILYUN blablablabla.

kalo kita execute

mysql> select xf_terbilang(999999999999999+1);

yang muncul malah :

+---------------------------------+
| xf_terbilang(999999999999999+1) |
+---------------------------------+
| SETRILYUN |
+---------------------------------+

dan utk angka2 di atas itu, yang muncul tetep sama : SETRILYUN.

hiks..

tp biarlah...
males ngobok2 lebih lanjut. *ntar lah klo ada case yang emang memerlukan "LEBIH" dari limitasi ini.

lagian siapa sih yang mo beli barang dengan nota tertulis di kuitansi sebesar 999 Trilyun?
hihihi.. Pemerintah mungkin. Wong 11 Trilyun aja tanpa pikir panjang berani 'dibuang' percuma utk nge-blokir akses situs-situs yang dianggap subversif.. Ups.., maap. Ini cuman intermezzo. Suer, saya bukan hacker. Belum. Dan saya ngga ada niat nge-post barang2 saru/porno di blog saya ini. *kecuali klo paragraf di atas sama pihak yang berwenang dianggap porno, yaa, meneketehe...

Utk solusi function terbilang di SQL Server 2000, mas Madhi pernah mempostingnya di sini. Silakan meluncur ke sana jika membutuhkan. Algoritma nya rada beda dengan punya saya, dan saya nggak mau mbandingin mana yang bagus mana yang jelek. Itu sama dengan perdebatan tiada ujung mengenai MS SQL Server vs MySQL. Ato blogger vs hacker (??) hehehe...

dan skrg. narcis mode = on.

Emang, abang eRQee emang CAKEP !!!

10 comments:

Anonim mengatakan...

Bang....!
Besok (pas lg nulis) sy punya tugas buatin function terbilang yang nyambung ke Ms. Exel.....

Uhhhh mana ga ngerti lagi...
Bisa dibantu ga Bang...!

kirimin ke email ya...
indra_drajat@yahoo.com

Anonim mengatakan...

chuking umpaT bapandir

mantap bujur wal'AI, BEhimat aku beKIKIHAN BEcari blOG NANG KAYA INI, RUpANYA Tasarukut DISINI SAKALINYA.. Truskan wal'lah.. aku handak umpat belAJAR..

Anonim mengatakan...

pak saya tidak ngerti dengan baris ke 15
while x <>
maksudnya apa?

Anonim mengatakan...

Oh rupanya terpotong
---
WHILE X <> LENGTH(sString) DO
---
Terima kasih banyak SPnya

Asep rohimat mengatakan...

wah makasih banyak mas atas bantuannya, kebetulan sy lg pusing bikin fungsi ini.

Anonim mengatakan...

kok hasilnya kosong yah?

Bullfrog mengatakan...

terima kasih banyak mas.

IZZAL mengatakan...

makasih mas

Abdul Basith mengatakan...

Cakeep ...
Bagaimana dengan ini coy.

mysql> select terbilang(REPEAT("9",39),0) as terbilang \G
*************************** 1. row ***************************
terbilang: 999,999,999,999,999,999,999,999,999,999,999,999,999

Sembilan Ratus Sembilan Puluh Sembilan Undecilyun
Sembilan Ratus Sembilan Puluh Sembilan Decilyun
Sembilan Ratus Sembilan Puluh Sembilan Nonilyun
Sembilan Ratus Sembilan Puluh Sembilan Octilyun
Sembilan Ratus Sembilan Puluh Sembilan Septilyun
Sembilan Ratus Sembilan Puluh Sembilan Sextilyun
Sembilan Ratus Sembilan Puluh Sembilan Quintilyun
Sembilan Ratus Sembilan Puluh Sembilan Quadrilyun
Sembilan Ratus Sembilan Puluh Sembilan Trilyun
Sembilan Ratus Sembilan Puluh Sembilan Milyar
Sembilan Ratus Sembilan Puluh Sembilan Juta
Sembilan Ratus Sembilan Puluh Sembilan Ribu
Sembilan Ratus Sembilan Puluh Sembilan
1 row in set, 1 warning (0.02 sec)

mysql>

Abdul Basith mengatakan...

Ini Linknya https://drive.google.com/open?id=1c83C6nPcCz-nigr3epLwmZDIvwr205h1

Posting Komentar