Fungsi & Prosedur Terbilang di MySQL
PROCEDURE :
DROP PROCEDURE IF EXISTS `p_terbilang`; DELIMITER ;; CREATE PROCEDURE `p_terbilang`(IN angka bigint, OUT retval TEXT) BEGIN -- @rgiapratama - 2017 DECLARE tmp1 TEXT; DECLARE tmp2 TEXT; SET max_sp_recursion_depth := 20; IF(angka = 0) THEN SET retval = ''; ELSEIF(angka < 12) THEN SET retval = ELT(angka,'satu','dua','tiga','empat','lima','enam','tujuh','delapan','sembilan','sepuluh','sebelas'); ELSEIF (angka < 20) THEN CALL p_terbilang((angka-10),tmp1); SET retval = CONCAT(tmp1,' belas'); ELSEIF (angka < 100) THEN CALL p_terbilang(FLOOR(angka/10),tmp1); CALL p_terbilang((angka%10),tmp2); SET retval = CONCAT(tmp1,' puluh ',tmp2); ELSEIf (angka < 200) THEN CALL p_terbilang((angka-100),tmp1); SET retval = CONCAT('seratus ',tmp1); ELSEIF (angka < 1000) THEN CALL p_terbilang(FLOOR(angka/100),tmp1); CALL p_terbilang((angka%100),tmp2); SET retval = CONCAT(tmp1,' ratus ',tmp2); ELSEIF (angka < 2000) THEN CALL p_terbilang((angka-1000),tmp1); SET retval = CONCAT('seribu ',tmp1); ELSEIF (angka < 1000000) THEN CALL p_terbilang(FLOOR(angka/1000),tmp1); CALL p_terbilang((angka%1000),tmp2); SET retval = CONCAT(tmp1,' ribu ',tmp2); ELSEIF (angka < 1000000000) THEN CALL p_terbilang(FLOOR(angka/1000000),tmp1); CALL p_terbilang((angka%1000000),tmp2); SET retval = CONCAT(tmp1,' juta ',tmp2); ELSEIF (angka < 1000000000000) THEN CALL p_terbilang(FLOOR(angka/1000000000),tmp1); CALL p_terbilang((angka%1000000000),tmp2); SET retval = CONCAT(tmp1,' milyar ',tmp2); ELSE SET retval = 'GIA'; END IF; END ;; DELIMITER ;
FUNCTION :
DROP FUNCTION IF EXISTS `f_terbilang`; DELIMITER ;; CREATE FUNCTION `f_terbilang`(angka BIGINT) RETURNS text CHARSET latin1 BEGIN -- @rgiapratama - 2017 DECLARE v_result TEXT; CALL p_terbilang(angka, v_result); RETURN REPLACE(v_result,' ',' '); END ;; DELIMITER ;
Hasil :
mysql> SELECT f_terbilang(19048500); +----------------------------------------------------------+ | f_terbilang(19048500) | +----------------------------------------------------------+ | sembilan belas juta empat puluh delapan ribu lima ratus | +----------------------------------------------------------+ 1 row in set (0.07 sec) mysql>
Comments