SQL QUERY : Membuat Fungsi Terbilang

Sering kali kita membuat laporan yang membutuhkan fungsi terbilang Seperti contoh Rp. 150.000 menjadi terbilang ( Seratu Lima Puluh Ribu Rupiah ) 

Berikut merupakan script untuk mengubah angka menjadi terbilang di SQL 

CREATE FUNCTION terbilang(@the_amount money)
RETURNS varchar(250) AS
BEGIN

DECLARE
    @divisor    bigint,
    @large_amount    money,
    @tiny_amount    money,
    @the_word    varchar(250),
    @dividen    money,
    @dummy        money,
    @weight    varchar(100),
    @unit        varchar(30),
    @follower    varchar(50),
    @prefix    varchar(10),
    @sufix        varchar(10)

--SET NOCOUNT ON
SET @the_word = ''
SET @large_amount = FLOOR(ABS(@the_amount) )
SET @tiny_amount = ROUND((ABS(@the_amount) - @large_amount ) * 100.00,0)
SET @divisor = 1000000000000.00

IF @large_amount > @divisor * 1000.00
    RETURN 'OUT OF RANGE'
   
WHILE @divisor >= 1
BEGIN       
    SET @dividen = FLOOR(@large_amount / @divisor)
    SET @large_amount = CONVERT(bigint,@large_amount) % @divisor
   
    SET @unit = ''
    IF @dividen > 0.00
        SET @unit=(CASE @divisor
            WHEN 1000000000000.00 THEN 'TRILYUN '
            WHEN 1000000000.00 THEN 'MILYAR '           
            WHEN 1000000.00 THEN 'JUTA '               
            WHEN 1000.00 THEN 'RIBU '
            ELSE @unit
        END )

    SET @weight = ''   
    SET @dummy = @dividen
    IF @dummy >= 100.00
        SET @weight = (CASE FLOOR(@dummy / 100.00)
            WHEN 1 THEN 'SE'
            WHEN 2 THEN 'DUA '
            WHEN 3 THEN 'TIGA '
            WHEN 4 THEN 'EMPAT '
            WHEN 5 THEN 'LIMA '
            WHEN 6 THEN 'ENAM '
            WHEN 7 THEN 'TUJUH '
            WHEN 8 THEN 'DELAPAN '
            ELSE 'SEMBILAN ' END ) + 'RATUS '

    SET @dummy = CONVERT(bigint,@dividen) % 100

    IF @dummy < 10.00
    BEGIN
        IF @dummy = 1.00 AND @unit = 'RIBU'
        BEGIN
            IF @dividen=@dummy
                SET @weight = @weight + 'SE'
            ELSE
                SET @weight = @weight + 'SATU '
        END
        ELSE
        IF @dummy > 0.00
            SET @weight = @weight + (CASE @dummy
                WHEN 1 THEN 'SATU '
                WHEN 2 THEN 'DUA '
                WHEN 3 THEN 'TIGA '
                WHEN 4 THEN 'EMPAT '
                WHEN 5 THEN 'LIMA '
                WHEN 6 THEN 'ENAM '
                WHEN 7 THEN 'TUJUH '
                WHEN 8 THEN 'DELAPAN '
                ELSE 'SEMBILAN ' END)
    END
    ELSE
    IF @dummy BETWEEN 11 AND 19
        SET @weight = @weight + (CASE CONVERT(bigint,@dummy) % 10
            WHEN 1 THEN 'SE'
            WHEN 2 THEN 'DUA '
            WHEN 3 THEN 'TIGA '
            WHEN 4 THEN 'EMPAT '
            WHEN 5 THEN 'LIMA '
            WHEN 6 THEN 'ENAM '
            WHEN 7 THEN 'TUJUH '
            WHEN 8 THEN 'DELAPAN '
            ELSE 'SEMBILAN ' END ) + 'BELAS '
    ELSE
    BEGIN
        SET @weight = @weight + (CASE FLOOR(@dummy / 10)
            WHEN 1 THEN 'SE'
            WHEN 2 THEN 'DUA '
            WHEN 3 THEN 'TIGA '
            WHEN 4 THEN 'EMPAT '
            WHEN 5 THEN 'LIMA '
            WHEN 6 THEN 'ENAM '
            WHEN 7 THEN 'TUJUH '
            WHEN 8 THEN 'DELAPAN '
            ELSE 'SEMBILAN ' END ) + 'PULUH '
        IF CONVERT(bigint,@dummy) % 10 > 0
            SET @weight = @weight + (CASE CONVERT(bigint,@dummy) % 10
                WHEN 1 THEN 'SATU '
                WHEN 2 THEN 'DUA '
                WHEN 3 THEN 'TIGA '
                WHEN 4 THEN 'EMPAT '
                WHEN 5 THEN 'LIMA '
                WHEN 6 THEN 'ENAM '
                WHEN 7 THEN 'TUJUH '
                WHEN 8 THEN 'DELAPAN '
                ELSE 'SEMBILAN ' END )
    END
   
    SET @the_word = @the_word + @weight + @unit
    SET @divisor = @divisor / 1000.00
END

IF FLOOR(@the_amount) = 0.00
    SET @the_word = 'NOL '

SET @follower = ''
IF @tiny_amount < 10.00
BEGIN   
    IF @tiny_amount > 0.00
        SET @follower = 'KOMA NOL ' + (CASE @tiny_amount
            WHEN 1 THEN 'SATU '
            WHEN 2 THEN 'DUA '
            WHEN 3 THEN 'TIGA '
            WHEN 4 THEN 'EMPAT '
            WHEN 5 THEN 'LIMA '
            WHEN 6 THEN 'ENAM '
            WHEN 7 THEN 'TUJUH '
            WHEN 8 THEN 'DELAPAN '
            ELSE 'SEMBILAN ' END)
END
ELSE
BEGIN
    SET @follower = 'KOMA ' + (CASE FLOOR(@tiny_amount / 10.00)
            WHEN 1 THEN 'SATU '
            WHEN 2 THEN 'DUA '
            WHEN 3 THEN 'TIGA '
            WHEN 4 THEN 'EMPAT '
            WHEN 5 THEN 'LIMA '
            WHEN 6 THEN 'ENAM '
            WHEN 7 THEN 'TUJUH '
            WHEN 8 THEN 'DELAPAN '
            ELSE 'SEMBILAN ' END)
    IF CONVERT(bigint,@tiny_amount) % 10 > 0
        SET @follower = @follower + (CASE CONVERT(bigint,@tiny_amount) % 10
            WHEN 1 THEN 'SATU '
            WHEN 2 THEN 'DUA '
            WHEN 3 THEN 'TIGA '
            WHEN 4 THEN 'EMPAT '
            WHEN 5 THEN 'LIMA '
            WHEN 6 THEN 'ENAM '
            WHEN 7 THEN 'TUJUH '
            WHEN 8 THEN 'DELAPAN '
            ELSE 'SEMBILAN ' END)
END
   
SET @the_word = @the_word + @follower

IF @the_amount < 0.00
    SET @the_word = 'MINUS ' + @the_word
   
RETURN @the_word + 'RUPIAH'
END

Untuk menjalankan function tersebut kita tinggal mengetikan perintah berikut

select dbo.terbilang(2500000) as terbilang

Semoga artikel ini membantu

Sumber : http://songkar13.blogspot.com/2013/10/membuat-fungsi-terbilang-di-sql-server.html