Change digits number into text on SQL Server 2000

Here’s and example that i want to show you to change digits number into text. This Function belongs to madhi, he make this function to create report with many pages and it can’t be done by creating parameter in reporting tools.

Here’s the Function :

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