CREATE FUNCTION [dbo].[AmountCent] ( @Money AS DECIMAL(30,2) )
RETURNS VARCHAR(1024)
AS
BEGIN
DECLARE @Number AS BIGINT
SET @Number = FLOOR(@Money)
DECLARE @Below20 TABLE
(
ID INT IDENTITY(0, 1) ,
Word VARCHAR(32)
)
DECLARE @Below100 TABLE
(
ID INT IDENTITY(2, 1) ,
Word VARCHAR(32)
)
INSERT @Below20
( Word )
VALUES ( 'Zero' )
INSERT @Below20
( Word )
VALUES ( 'One' )
INSERT @Below20
( Word )
VALUES ( 'Two' )
INSERT @Below20
( Word )
VALUES ( 'Three' )
INSERT @Below20
( Word )
VALUES ( 'Four' )
INSERT @Below20
( Word )
VALUES ( 'Five' )
INSERT @Below20
( Word )
VALUES ( 'Six' )
INSERT @Below20
( Word )
VALUES ( 'Seven' )
INSERT @Below20
( Word )
VALUES ( 'Eight' )
INSERT @Below20
( Word )
VALUES ( 'Nine' )
INSERT @Below20
( Word )
VALUES ( 'Ten' )
INSERT @Below20
( Word )
VALUES ( 'Eleven' )
INSERT @Below20
( Word )
VALUES ( 'Twelve' )
INSERT @Below20
( Word )
VALUES ( 'Thirteen' )
INSERT @Below20
( Word )
VALUES ( 'Fourteen' )
INSERT @Below20
( Word )
VALUES ( 'Fifteen' )
INSERT @Below20
( Word )
VALUES ( 'Sixteen' )
INSERT @Below20
( Word )
VALUES ( 'Seventeen' )
INSERT @Below20
( Word )
VALUES ( 'Eighteen' )
INSERT @Below20
( Word )
VALUES ( 'Nineteen' )
INSERT @Below100
VALUES ( 'Twenty' )
INSERT @Below100
VALUES ( 'Thirty' )
INSERT @Below100
VALUES ( 'Forty' )
INSERT @Below100
VALUES ( 'Fifty' )
INSERT @Below100
VALUES ( 'Sixty' )
INSERT @Below100
VALUES ( 'Seventy' )
INSERT @Below100
VALUES ( 'Eighty' )
INSERT @Below100
VALUES ( 'Ninety' )
DECLARE @English VARCHAR(1024)
SET @English = ( SELECT CASE WHEN @Number = 0 THEN ''
WHEN @Number BETWEEN 1 AND 19
THEN ( SELECT Word
FROM @Below20
WHERE ID = @Number
)
WHEN @Number BETWEEN 20 AND 99
-- SQL Server recursive function
THEN ( SELECT Word
FROM @Below100
WHERE ID = @Number / 10
) + '-'
+ dbo.AmountCent(@Number % 10)
WHEN @Number BETWEEN 100 AND 999
THEN ( dbo.AmountCent(@Number / 100) )
+ ' Hundred '
+ dbo.AmountCent(@Number % 100)
WHEN @Number BETWEEN 1000 AND 999999
THEN ( dbo.AmountCent(@Number / 1000) )
+ ' Thousand '
+ dbo.AmountCent(@Number % 1000)
WHEN @Number BETWEEN 1000000 AND 999999999
THEN ( dbo.AmountCent(@Number / 1000000) )
+ ' Million '
+ dbo.AmountCent(@Number % 1000000)
ELSE ' INVALID INPUT'
END
)
SELECT @English = RTRIM(@English)
SELECT @English = RTRIM(LEFT(@English, LEN(@English) - 1))
WHERE RIGHT(@English, 1) = '-'
IF @@NestLevel = 1
BEGIN
SELECT @English = @English
END
RETURN (@English)
END
0 Comments