Fungsi Terbilang di SQL Server

reposting from my previous blog (mamadh nge-Blog)
Kemaren dapet kasus untuk menampilkan terbilang dalam bahasa indonesia di Crystal Report. klo reportnya hanya 1 halaman, bisa dengan cara diproses di vb dulu, baru di lempar ke parameter field di CR. untuk report yg lebih dari 1 halaman, dan masing2x halaman mewakili record yg berbeda, terbilangnya tidak bisa di lewatkan parameter field, sebab, klo dilewatkan parameter field, antara halaman 1 dengan yg lain, terbilangnya akan sama, walaupun nilai rupiah nya berbeda.
untuk mengatasi hal tersebut, akhirnya dibuat saja fungsi terbilang di sql server. sehingga terbilangnya akan muncul di query nya.
Fungsi terbilang ini dibuat oleh mas Arif Hidayat.

CREATE FUNCTION [DBO].[TerbilangDetil] (@Nilai as decimal)
RETURNS varchar(200) AS
BEGIN
declare @TS varchar(200), @ST varchar(200), @S integer, @p integer, @r1 integer, @Bil varchar(200), @TP varchar(200), @hasil varchar(200)
set @ST = Right(rTrim(Str(@Nilai + 1000)), 3)
set @S = cast(Right(@ST, 1) as integer)
set @p = cast(substring(@ST, 2, 1) as integer)
set @r1 = cast(Left(@ST, 1) as integer)
set @Bil = ‘Satu Dua Tiga Empat Lima ‘
set @Bil = @Bil + ‘Enam Tujuh Delapan Sembilan’

If @S = 0
set @TS = ”
Else
set @TS = rTrim(substring(@Bil, @S * 8 - 7, 8)) + ‘ ‘

If @p = 0
set @TP = @TS
Else
If @p = 1
If @S = 0
set @TP = ‘Sepuluh ‘
Else
If @S = 1
set @TP = ‘Sebelas ‘
Else
set @TP = rTrim(substring(@Bil, @S * 8 - 7, 8)) + ‘ Belas ‘
Else
set @TP = rTrim(substring(@Bil, @p * 8 - 7, 8)) + ‘ Puluh ‘ + @TS

If @r1 = 0
set @hasil = @TP
Else
If @r1 = 1
set @hasil = ‘Seratus ‘ + @TP
Else
set @hasil = rTrim(substring(@Bil, @r1 * 8 - 7, 8)) + ‘ Ratus ‘ + @TP
return @hasil
END

CREATE FUNCTION [dbo].[terbilang] (@angka as decimal(19,2))
RETURNS varchar (200) AS
BEGIN
Declare @b Decimal, @r Decimal, @J Decimal, @M Decimal, @T1 Decimal, @koma1 Decimal, @koma2 Integer, @hasil varchar(200)
Declare @s_t varchar(200), @ribu varchar(200), @Juta varchar(200), @Miliar varchar(200), @tx varchar(200), @angka1 varchar(200), @tx1 varchar(200)
set @s_t = case when Len(rTrim(cast(cast(@Angka as bigint) as varchar(20)))) < 15
then replicate(’0′,15 - Len(rTrim(cast(cast(@Angka as bigint) as varchar(20))))) + rTrim(cast(cast(@Angka as bigint) as varchar(20)))
else rTrim(cast(cast(@Angka as bigint) as varchar(20)))
end
–set @z = cast(15 - Len(rTrim(cast(cast(@Angka as integer) as varchar(20)))) as varchar(20))
–+ rTrim(cast(cast(@Angka as integer) as varchar(20)))
set @r = cast(Right(@s_t, 3) as integer)
set @b = cast(substring(@s_t, 10, 3) as integer)
set @J = cast(substring(@s_t, 7, 3) as integer)
set @M = cast(substring(@s_t, 4, 3) as integer)
set @T1 = cast(Left(@s_t, 3) as integer)
set @ribu = case when @b = 0 then ‘ ‘ else case when @b = 1 then ‘Seribu’ else [dbo].TerbilangDetil(@b) + ‘Ribu’ end end
set @Juta = case when @J = 0 then ‘ ‘ else [dbo].TerbilangDetil(@J) + ‘Juta ‘ end
set @Miliar = case when @M = 0 then ‘ ‘ else [dbo].TerbilangDetil(@M) + ‘Miliar’ end
set @tx = Case
when @Angka < 1000 –&& Ratusan
then [dbo].TerbilangDetil(@r)
when @Angka < 1000000 –&& Ribuan
then @ribu + ‘ ‘ + [dbo].TerbilangDetil(@r)
when @Angka < 1000000000 –&& Jutaan
then [dbo].TerbilangDetil(@J) + ” + rTrim(’Juta ‘ + @ribu) + ‘ ‘ + [dbo].TerbilangDetil(@r)
when @Angka < 1000000000000 –&& Miliaran
then [dbo].TerbilangDetil(@M) + ” + rTrim(’Miliar ‘ + @Juta + @ribu) + ‘ ‘ + [dbo].TerbilangDetil(@r)
when @Angka >= 1000000000000 –&& Trilliun
then [dbo].TerbilangDetil(@T1) + ” + rTrim(’Trilliun ‘ + @Miliar + @Juta + @ribu) + ‘ ‘ + [dbo].TerbilangDetil(@r)
End
set @angka1 = rTrim(cast(@Angka as varchar(20)))
set @koma1 = case when charindex(’.', @angka1) = 0 then 0 else cast(rTrim(substring(@angka1, charindex(’.',@angka1) + 1, 2)) as integer) end
set @koma2 = Len(case when charindex(’.',@angka1) = 0 then ‘0′ else rTrim(substring(@angka1, charindex(’.',@angka1) + 1, 2)) end)
If @koma1 > 0
begin
If @koma1 < 10 And @koma2 = 1
set @koma1 = @koma1 * 10
set @tx1 = [dbo].TerbilangDetil(@koma1)
set @hasil = @tx + ‘Rupiah ‘ + @tx1 + ‘Sen’
end
Else
set @hasil = @tx + ‘Rupiah’
return (@hasil)
END

Powered by ScribeFire.