USE <Database_Name>
GO
IF OBJECT_ID(N'udf__TerbilangHundred') IS NOT NULL
DROP FUNCTION udf__TerbilangHundred
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- *****************************************************************************
-- Name : udf__TerbilangHundred
-- Author : Chandra Gunawan
-- Date : 19-Nov-11
-- Description : Return the Terbilang (number to text in Bahasa)
-- for each hundred base
-- Parameters :
-- @pHundedValue decimal Integer value in hundred base
--
-- Usage Sample :
-- -------------------------------------------------
-- SELECT dbo.udf__TerbilangHundred(121)
-- --> Result: Seratus Dua Puluh Satu
-- --> Result: Seratus Dua Puluh Satu
-- -------------------------------------------------
--
-- Maintenance Log
-- =============================================================================
-- Date ID Description
-- -----------------------------------------------------------------------------
-- 19-Nov-11 chg Initial Revision
-- *****************************************************************************
CREATE FUNCTION [dbo].[udf__TerbilangHundred]
(
@pHundredValue AS decimal
)
RETURNS varchar(200) AS
BEGIN
DECLARE @iS integer,
@iP integer,
@iR1 integer,
@cTS varchar(200),
@cST varchar(200),
@cTP varchar(200),
@cBil varchar(200),
@cHasil varchar(200)
SELECT @cST = RIGHT(RTRIM(STR(@pHundredValue + 1000)), 3),
@iS = CAST(RIGHT(@cST, 1) AS integer),
@iP = CAST(SUBSTRING(@cST, 2, 1) AS integer),
@iR1 = CAST(LEFT(@cST, 1) AS integer),
@cBil = 'Satu Dua Tiga Empat Lima ' +
'Enam Tujuh Delapan Sembilan'
IF @iS = 0
SET @cTS = ''
ELSE
SET @cTS = RTRIM(SUBSTRING(@cBil, @iS * 8 - 7, 8)) + ' '
IF @iP = 0
SET @cTP = @cTS
ELSE
IF @iP = 1
IF @iS = 0
SET @cTP = 'Sepuluh '
ELSE
IF @iS = 1
SET @cTP = 'Sebelas '
ELSE
SET @cTP = RTRIM(SUBSTRING(@cBil, @iS * 8 - 7, 8)) + ' Belas '
ELSE
SET @cTP = RTRIM(SUBSTRING(@cBil, @iP * 8 - 7, 8)) + ' Puluh ' + @cTS
IF @iR1 = 0
SET @cHasil = @cTP
ELSE
IF @iR1 = 1
SET @cHasil = 'Seratus ' + @cTP
ELSE
SET @cHasil = RTRIM(SUBSTRING(@cBil, @iR1 * 8 - 7, 8)) + ' Ratus ' + @cTP
RETURN @cHasil
END
GO
GRANT EXECUTE ON dbo.udf__TerbilangHundred TO public
GO
IF OBJECT_ID(N'udf__Terbilang') IS NOT NULL
DROP FUNCTION udf__Terbilang
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- *****************************************************************************
-- Name : udf__Terbilang
-- Author : Chandra Gunawan
-- Date : 19-Nov-11
-- Description : Return the Terbilang (number to text in Bahasa)
-- for input decimal value
-- Parameters :
-- @pNumber dec Value to be converted
-- @pCurrency vchar(10) (optional) The currecncy name - default: Rupiah
-- @pCent vchar(10) (optional) The cent name - default: Sen
--
-- Usage Sample :
-- -------------------------------------------------
-- SELECT dbo.udf__Terbilang(1231211.34, default, default)
-- --> Result: Sebelas Ribu Dua Ratus Sebelas Rupiah Tiga Puluh Empat Sen
-- --> Result: Sebelas Ribu Dua Ratus Sebelas Rupiah Tiga Puluh Empat Sen
-- -------------------------------------------------
--
-- Maintenance Log
-- =============================================================================
-- Date ID Description
-- -----------------------------------------------------------------------------
-- 19-Nov-11 chg Initial Revision
-- *****************************************************************************
CREATE FUNCTION [dbo].[udf__Terbilang]
(
@pNumber decimal(19,2),
@pCurrency varchar(10) = 'Rupiah',
@pCent varchar(10) = 'Sen'
)
RETURNS varchar (200) AS
BEGIN
DECLARE @dB decimal,
@dR decimal,
@dJ decimal,
@dM decimal,
@dT1 decimal,
@dKoma1 decimal,
@iKoma2 integer,
@cHasil varchar(200),
@cS_T varchar(200),
@cRibu varchar(200),
@cJuta varchar(200),
@cMiliar varchar(200),
@cTx varchar(200),
@cAngka1 varchar(200),
@cTx1 varchar(200),
@cZ varchar(250)
SET @cS_T = CASE
WHEN Len(RTRIM(CAST(CAST(@pNumber AS bigint) AS varchar(20)))) < 15
THEN REPLICATE('0',15 - Len(RTRIM(CAST(CAST(@pNumber AS bigint) AS varchar(20))))) + RTRIM(CAST(CAST(@pNumber AS bigint) AS varchar(20)))
ELSE RTRIM(CAST(CAST(@pNumber AS bigint) AS varchar(20)))
END
SET @cZ = CAST(15 - Len(RTRIM(CAST(CAST(@pNumber AS integer) AS varchar(20)))) AS varchar(20)) +
RTRIM(CAST(CAST(@pNumber AS integer) AS varchar(20)))
SET @dR = CAST(RIGHT(@cS_T, 3) AS integer)
SET @dB = CAST(SUBSTRING(@cS_T, 10, 3) AS integer)
SET @dJ = CAST(SUBSTRING(@cS_T, 7, 3) AS integer)
SET @dM = CAST(SUBSTRING(@cS_T, 4, 3) AS integer)
SET @dT1 = CAST(LEFT(@cS_T, 3) AS integer)
SET @cRibu = CASE
WHEN @dB = 0 THEN ' '
ELSE
CASE
WHEN @dB = 1 THEN 'Seribu'
ELSE [dbo].udf__TerbilangHundred(@dB) + 'Ribu'
END
END
SET @cJuta = CASE
WHEN @dJ = 0 THEN ' '
ELSE [dbo].udf__TerbilangHundred(@dJ) + 'Juta '
END
SET @cMiliar = CASE
WHEN @dM = 0 THEN ' '
ELSE [dbo].udf__TerbilangHundred(@dM) + 'Miliar'
END
SET @cTx = CASE
WHEN @pNumber < 1000 --Ratusan
THEN [dbo].udf__TerbilangHundred(@dR)
WHEN @pNumber < 1000000 --Ribuan
THEN @cRibu + ' ' + [dbo].udf__TerbilangHundred(@dR)
WHEN @pNumber < 1000000000 --Jutaan
THEN [dbo].udf__TerbilangHundred(@dJ) + '' + RTRIM('Juta ' + @cRibu) + ' ' + [dbo].udf__TerbilangHundred(@dR)
WHEN @pNumber < 1000000000000 --Miliaran
THEN [dbo].udf__TerbilangHundred(@dM) + '' + RTRIM('Miliar ' + @cJuta + @cRibu) + ' ' + [dbo].udf__TerbilangHundred(@dR)
WHEN @pNumber >= 1000000000000 --Trilliun
THEN [dbo].udf__TerbilangHundred(@dT1) + '' + RTRIM('Trilliun ' + @cMiliar + @cJuta + @cRibu) + ' ' + [dbo].udf__TerbilangHundred(@dR)
END
SET @cAngka1 = RTRIM(CAST(@pNumber AS varchar(20)))
SET @dKoma1 = CASE WHEN charindex('.', @cAngka1) = 0
THEN 0
ELSE CAST(RTRIM(SUBSTRING(@cAngka1, charindex('.',@cAngka1) + 1, 2)) AS integer)
END
SET @iKoma2 = Len(CASE WHEN charindex('.',@cAngka1) = 0
THEN '0'
ELSE RTRIM(SUBSTRING(@cAngka1, charindex('.',@cAngka1) + 1, 2))
END)
IF @dKoma1 > 0
BEGIN
IF @dKoma1 < 10 AND @iKoma2 = 1
SET @dKoma1 = @dKoma1 * 10
SET @cTx1 = [dbo].udf__TerbilangHundred(@dKoma1)
SET @cHasil = @cTx + @pCurrency + ' ' + @cTx1 + @pCent
END
ELSE
SET @cHasil = @cTx + @pCurrency
RETURN (@cHasil)
END
GO
GRANT EXECUTE ON dbo.udf__Terbilang TO public
GO
