T-SQL: Konversi Angka ke Terbilang

Berikut ini adalah fungsi query Microsoft SQL Server untuk mengubah angka menjadi terbilang. Misalnya, angka “999.95” akan dikonversi menjadi “Sembilan ratus sembilan puluh sembilan koma sembilan lima”. Cara penggunaannya sebagai berikut:

SELECT dbo.Terbilang(999.95)

Fungsi Terbilang tersebut menerima input NUMERIC(19,6) dan mengembalikan nilai bertipe NVARCHAR(MAX). Berikut adalah kode lengkapnya:

CREATE FUNCTION [dbo].[Terbilang](@number NUMERIC(19,6))

RETURNS NVARCHAR(MAX)

AS

BEGIN

    DECLARE 

    @position INT, 

    @length INT, 

    @words NVARCHAR(MAX), 

    @ends NVARCHAR(MAX), 

    @numStr NVARCHAR(MAX), 

    @foreStr NVARCHAR(MAX), 

    @backStr NVARCHAR(MAX), 

    @char NVARCHAR(1),

    @charafter NVARCHAR(1),

    @charprev NVARCHAR(1),

    @charprev2 NVARCHAR(1)

 

SELECT @numStr = STR(@number, 19, 2)

SELECT @numStr = LTRIM(RTRIM(@numStr))

SELECT @foreStr = SUBSTRING(@numStr, 0, (SELECT CHARINDEX('.', @numStr, 1)))

SELECT @backStr = SUBSTRING(@numStr, (SELECT CHARINDEX('.', @numStr, 1)+1), LEN(@numStr))

SELECT @length = LEN(@foreStr)

SELECT @position = @length

SELECT @words =''

 

    --Memproses "angka di depan koma" 

    WHILE(@position > 0)

    BEGIN    

            

            SELECT @char = SUBSTRING(@numStr, @length+1 - @position, 1)

            SELECT @charafter = SUBSTRING(@numStr, @length+2 - @position, 1)

            SELECT @charprev = SUBSTRING(@numStr, @length - @position, 1)

            SELECT @charprev2 = SUBSTRING(@numStr, @length - @position - 1, 1)

            

            IF ((@char = '1') AND ((SELECT(@position-1)/3.0) = 1) AND 

                (@charafter != '' ) AND ((SELECT CAST(@charprev as INT)) = 0)) 

                SELECT @words = @words + 'se' 

            ELSE

            IF ((@char = '1') AND ((SELECT @position % 3) = 1)) 

                SELECT @words = @words + 'satu ' 

            ELSE

            IF ((@char = '1') AND ((SELECT CAST(@charafter as INT)) > 1) AND 

                ((SELECT @position % 3) = 2))

                BEGIN

                    IF (@charafter = '1') SELECT @words = @words + 'se' ELSE

                    IF (@charafter = '2') SELECT @words = @words + 'dua ' ELSE

                    IF (@charafter = '3') SELECT @words = @words + 'tiga ' ELSE    

                    IF (@charafter = '4') SELECT @words = @words + 'empat ' ELSE    

                    IF (@charafter = '5') SELECT @words = @words + 'lima ' ELSE    

                    IF (@charafter = '6') SELECT @words = @words + 'enam ' ELSE    

                    IF (@charafter = '7') SELECT @words = @words + 'tujuh ' ELSE    

                    IF (@charafter = '8') SELECT @words = @words + 'delapan ' ELSE    

                    IF (@charafter = '9') SELECT @words = @words + 'sembilan '

                END

            ELSE

            IF (@char = '1') SELECT @words = @words + 'se' ELSE

            IF (@char = '2') SELECT @words = @words + 'dua ' ELSE

            IF (@char = '3') SELECT @words = @words + 'tiga ' ELSE    

            IF (@char = '4') SELECT @words = @words + 'empat ' ELSE    

            IF (@char = '5') SELECT @words = @words + 'lima ' ELSE    

            IF (@char = '6') SELECT @words = @words + 'enam ' ELSE    

            IF (@char = '7') SELECT @words = @words + 'tujuh ' ELSE    

            IF (@char = '8') SELECT @words = @words + 'delapan ' ELSE    

            IF (@char = '9') SELECT @words = @words + 'sembilan ' ELSE

            IF ((@char = '0') AND ((SELECT CAST(@charprev as INT)) > 1) AND ((SELECT @position % 3) = 1))

                SELECT @words = @words 

            ELSE

            IF ((@char = '0') AND ((SELECT @charprev) = '0') AND ((SELECT CAST(@charprev2 as INT)) > 0) AND ((SELECT @position % 3) = 1))

                SELECT @words = @words 

            ELSE

            IF (@char = '0') 

            BEGIN

                SELECT @position = @position - 1

                CONTINUE

            END     

            

            IF ((SELECT @position % 3) = 0) SELECT @words = @words + 'ratus ' ELSE    

            IF (((SELECT @position % 3) = 2) AND ((SELECT CAST(@char as INT)) > 1)) 

                SELECT @words = @words + 'puluh ' 

            ELSE    

            IF (((SELECT @position % 3) = 2) AND ((SELECT CAST(@char as INT)) = 1)

                AND ((SELECT CAST(@charafter as INT)) > 0))

            BEGIN 

                SELECT @words = @words + 'belas '

                SELECT @position = @position - 1

            END

            ELSE

            IF (((SELECT @position % 3) = 2) AND ((SELECT CAST(@char as INT)) = 1)

                AND ((SELECT CAST(@charafter as INT)) = 0))

            BEGIN 

                SELECT @words = @words + 'puluh '

                SELECT @position = @position - 1

            END

            

            IF ((SELECT (@position-1)/3.0) = 1) SELECT @words = @words +'ribu ' ELSE

            IF ((SELECT (@position-1)/3.0) = 2) SELECT @words = @words +'juta ' ELSE

            IF ((SELECT (@position-1)/3.0) = 3) SELECT @words = @words +'milyar ' ELSE

            IF ((SELECT (@position-1)/3.0) = 4) SELECT @words = @words +'triliun '

 

        SELECT @position = @position - 1

    END

    

    --Memproses "koma" dan "angka di belakang koma"

    IF((SELECT CAST(@backStr AS INT)) > 0)

    BEGIN

        --Menambahkan "koma" pada terbilang

        

        SELECT @words = @words + 'koma '

        

        --Menambahkan "Angka di belakang koma" pada terbilang

    

        SELECT @length = LEN(@backStr)

        SELECT @position = @length

        

        WHILE( @position > 0)

        BEGIN

        

            SELECT @char = SUBSTRING(@backStr, @length+1 - @position, 1)

            SELECT @words = @words +

            (CASE @char

                WHEN '0'THEN 'nol '

                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 '

                WHEN '9'THEN 'sembilan '

                ELSE ''

             END

            )    

            SELECT @position = @position - 1

        END 

    END

    

    SELECT @words = LTRIM(RTRIM(@words))

    

    -- Huruf pertama huruf besar

    IF LEN(@words) > 0 

    BEGIN

        SET @words = UPPER(left(@words,1)) + RIGHT(@words, LEN(@words)-1)

    END

 

    /* FINAL RETURN */

RETURN (SELECT @words)

END


2 Comments on “T-SQL: Konversi Angka ke Terbilang”

  1. Cgt says:

    Jika ada yang ingin terbilang ke dalam bahasa Inggris ini modifikasi dari function diatas .. terima kasih kepada original authornya ya. Thanks

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER FUNCTION [dbo].[Number_Says]
    (@number NUMERIC(19,6))
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
    DECLARE
    @position INT,
    @length INT,
    @words NVARCHAR(MAX),
    @ends NVARCHAR(MAX),
    @numStr NVARCHAR(MAX),
    @foreStr NVARCHAR(MAX),
    @backStr NVARCHAR(MAX),
    @char NVARCHAR(1),
    @charafter NVARCHAR(1),
    @charprev NVARCHAR(1),
    @charprev2 NVARCHAR(1)

    SELECT @numStr = STR(@number, 19, 2)
    SELECT @numStr = LTRIM(RTRIM(@numStr))
    SELECT @foreStr = SUBSTRING(@numStr, 0, (SELECT CHARINDEX(‘.’, @numStr, 1)))
    SELECT @backStr = SUBSTRING(@numStr, (SELECT CHARINDEX(‘.’, @numStr, 1)+1), LEN(@numStr))
    SELECT @length = LEN(@foreStr)
    SELECT @position = @length
    SELECT @words =”

    –Memproses “angka di depan koma”
    WHILE(@position > 0)
    BEGIN

    SELECT @char = SUBSTRING(@numStr, @length+1 – @position, 1)
    SELECT @charafter = SUBSTRING(@numStr, @length+2 – @position, 1)
    SELECT @charprev = SUBSTRING(@numStr, @length – @position, 1)
    SELECT @charprev2 = SUBSTRING(@numStr, @length – @position – 1, 1)

    IF ((@char = ‘1’) AND ((SELECT(@position-1)/3.0) = 1) AND
    (@charafter != ” ) AND ((SELECT CAST(@charprev as INT)) = 0))
    SELECT @words = @words + ‘one’
    ELSE
    IF ((@char = ‘1’) AND ((SELECT @position % 3) = 1))
    SELECT @words = @words + ‘one ‘
    ELSE
    IF ((@char = ‘1’) AND ((SELECT CAST(@charafter as INT)) > 1) AND
    ((SELECT @position % 3) = 2))
    BEGIN
    IF (@charafter = ‘1’) SELECT @words = @words + ‘one’ ELSE
    IF (@charafter = ‘2’) SELECT @words = @words + ‘two ‘ ELSE
    IF (@charafter = ‘3’) SELECT @words = @words + ‘three ‘ ELSE
    IF (@charafter = ‘4’) SELECT @words = @words + ‘four ‘ ELSE
    IF (@charafter = ‘5’) SELECT @words = @words + ‘five ‘ ELSE
    IF (@charafter = ‘6’) SELECT @words = @words + ‘six ‘ ELSE
    IF (@charafter = ‘7’) SELECT @words = @words + ‘seven ‘ ELSE
    IF (@charafter = ‘8’) SELECT @words = @words + ‘eight ‘ ELSE
    IF (@charafter = ‘9’) SELECT @words = @words + ‘nine ‘
    END
    ELSE
    IF (@char = ‘1’) SELECT @words = @words + ‘one’ ELSE
    IF (@char = ‘2’) SELECT @words = @words + ‘two’ ELSE
    IF (@char = ‘3’) SELECT @words = @words + ‘three’ ELSE
    IF (@char = ‘4’) SELECT @words = @words + ‘four’ ELSE
    IF (@char = ‘5’) SELECT @words = @words + ‘five’ ELSE
    IF (@char = ‘6’) SELECT @words = @words + ‘six’ ELSE
    IF (@char = ‘7’) SELECT @words = @words + ‘seven’ ELSE
    IF (@char = ‘8’) SELECT @words = @words + ‘eight’ ELSE
    IF (@char = ‘9’) SELECT @words = @words + ‘nine’ ELSE
    IF ((@char = ‘0’) AND ((SELECT CAST(@charprev as INT)) > 1) AND ((SELECT @position % 3) = 1))
    SELECT @words = @words
    ELSE
    IF ((@char = ‘0’) AND ((SELECT @charprev) = ‘0’) AND ((SELECT CAST(@charprev2 as INT)) > 0) AND ((SELECT @position % 3) = 1))
    SELECT @words = @words
    ELSE
    IF (@char = ‘0’)
    BEGIN
    SELECT @position = @position – 1
    CONTINUE
    END

    IF ((SELECT @position % 3) = 0) SELECT @words = @words + ‘ hundred ‘ ELSE
    IF (((SELECT @position % 3) = 2) AND ((SELECT CAST(@char as INT)) > 1))
    SELECT @words = @words + ‘ty ‘
    ELSE
    IF (((SELECT @position % 3) = 2) AND ((SELECT CAST(@char as INT)) = 1)
    AND ((SELECT CAST(@charafter as INT)) > 0))
    BEGIN
    SELECT @words = @words + ‘ teen ‘
    SELECT @position = @position – 1
    END
    ELSE
    IF (((SELECT @position % 3) = 2) AND ((SELECT CAST(@char as INT)) = 1)
    AND ((SELECT CAST(@charafter as INT)) = 0))
    BEGIN
    SELECT @words = @words + ‘ty ‘
    SELECT @position = @position – 1
    END

    IF ((SELECT (@position-1)/3.0) = 1) SELECT @words = @words +’ thousand ‘ ELSE
    IF ((SELECT (@position-1)/3.0) = 2) SELECT @words = @words +’ million ‘ ELSE
    IF ((SELECT (@position-1)/3.0) = 3) SELECT @words = @words +’ trillion ‘ ELSE
    IF ((SELECT (@position-1)/3.0) = 4) SELECT @words = @words +’ billion ‘

    SELECT @position = @position – 1
    END

    –Memproses “koma” dan “angka di belakang koma”
    IF((SELECT CAST(@backStr AS INT)) > 0)
    BEGIN
    –Menambahkan “koma” pada terbilang

    SELECT @words = @words + ‘ point ‘

    –Menambahkan “Angka di belakang koma” pada terbilang

    SELECT @length = LEN(@backStr)
    SELECT @position = @length

    WHILE( @position > 0)
    BEGIN

    SELECT @char = SUBSTRING(@backStr, @length+1 – @position, 1)
    SELECT @words = @words +
    (CASE @char
    WHEN ‘0’THEN ‘zero ‘
    WHEN ‘1’THEN ‘one ‘
    WHEN ‘2’THEN ‘two ‘
    WHEN ‘3’THEN ‘three ‘
    WHEN ‘4’THEN ‘four ‘
    WHEN ‘5’THEN ‘five ‘
    WHEN ‘6’THEN ‘six ‘
    WHEN ‘7’THEN ‘seven ‘
    WHEN ‘8’THEN ‘eight ‘
    WHEN ‘9’THEN ‘nine ‘
    ELSE ”
    END
    )
    SELECT @position = @position – 1
    END
    END

    SELECT @words = LTRIM(RTRIM(@words))

    — Rubah 11, 12, 13, 10, 20, 30
    SELECT @words = REPLACE(@words, ‘one teen’, ‘eleven’)
    SELECT @words = REPLACE(@words, ‘two teen’, ‘twelve’)
    SELECT @words = REPLACE(@words, ‘three teen’, ‘thirteen’)
    SELECT @words = REPLACE(@words, ‘onety’, ‘ten’)
    SELECT @words = REPLACE(@words, ‘twoty’, ‘twenty’)
    SELECT @words = REPLACE(@words, ‘threety’, ‘thirty’)

    — Huruf pertama huruf besar
    IF LEN(@words) > 0
    BEGIN
    SET @words = UPPER(left(@words,1)) + RIGHT(@words, LEN(@words)-1)
    END

    /* FINAL RETURN */
    RETURN (SELECT @words)
    END

  2. wong says:

    wah terima kasih banget : )
    ijin nyontek kk


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