Archive for February, 2011

udf function to return words for money

February 14, 2011

We started using this function because crystal reports has a nice formatting function to do the number to words conversion, but I think that Microsoft SQL Reporting doesn’t have such a function.

modified from the original to allow billions and also negatives

examples:
SELECT [dbo].[fnMoneyToEnglish] ( -999999999999.12 )
returns
( Nine Hundred Ninety-Nine Billion Nine Hundred Ninety-Nine Million Nine Hundred Ninety-Nine Thousand Nine Hundred Ninety-Nine Dollars and 12 Cents )

SELECT [dbo].[fnMoneyToEnglish] (-10.11)
returns
( Ten Dollars and 11 Cents )


USE [BursarReceipt]
GO

/****** Object: UserDefinedFunction [dbo].[fnMoneyToEnglish] Script Date: 02/14/2011 11:29:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- http://www.sqlusa.com/bestpractices2005/moneyformat/
-- SQL convert dollar amount into words for check printing - Dollars and cents format
-- Convert numbers to words - SQL amount into words - Currency to words
-- SQL money format to English - Translate money to text
-- Translate dollar amount to words - Convert numbers into English words
ALTER FUNCTION [dbo].[fnMoneyToEnglish](@Money AS Money)

RETURNS VARCHAR(1024)

AS

BEGIN
DECLARE @Number as BIGINT
DECLARE @MinusFlag as Bit

if @Money < 0
begin
set @Money = -1 * @Money
Set @MinusFlag = 1
end

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 into @Below20 (Word) VALUES ( 'Zero')
INSERT into @Below20 (Word) VALUES ('One')
INSERT into @Below20 (Word) VALUES ('Two')
INSERT into @Below20 (Word) VALUES ('Three')
INSERT into @Below20 (Word) VALUES ('Four')
INSERT into @Below20 (Word) VALUES ('Five')
INSERT into @Below20 (Word) VALUES ('Six')
INSERT into @Below20 (Word) VALUES ('Seven')
INSERT into @Below20 (Word) VALUES ('Eight')
INSERT into @Below20 (Word) VALUES ('Nine')
INSERT into @Below20 (Word) VALUES ('Ten')
INSERT into @Below20 (Word) VALUES ('Eleven')
INSERT into @Below20 (Word) VALUES ('Twelve')
INSERT into @Below20 (Word) VALUES ('Thirteen')
INSERT into @Below20 (Word) VALUES ('Fourteen')
INSERT into @Below20 (Word) VALUES ('Fifteen')
INSERT into @Below20 (Word) VALUES ('Sixteen')
INSERT into @Below20 (Word) VALUES ('Seventeen')
INSERT into @Below20 (Word) VALUES ('Eighteen')
INSERT into @Below20 (Word) VALUES ('Nineteen')

INSERT into @Below100 (Word) VALUES ('Twenty')
INSERT into @Below100 (Word) VALUES ('Thirty')
INSERT into @Below100 (Word) VALUES ('Forty')
INSERT into @Below100 (Word) VALUES ('Fifty')
INSERT into @Below100 (Word) VALUES ('Sixty')
INSERT into @Below100 (Word) VALUES ('Seventy')
INSERT into @Below100 (Word) VALUES ('Eighty')
INSERT into @Below100 (Word) VALUES ('Ninety')

DECLARE @English varchar(1024)
(
SELECT @English =
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.fnMoneyToEnglish( @Number % 10)

WHEN @Number BETWEEN 100 AND 999

THEN (dbo.fnMoneyToEnglish( @Number / 100))+' Hundred '+

dbo.fnMoneyToEnglish( @Number % 100)

WHEN @Number BETWEEN 1000 AND 999999

THEN (dbo.fnMoneyToEnglish( @Number / 1000))+' Thousand '+

dbo.fnMoneyToEnglish( @Number % 1000)

WHEN @Number BETWEEN 1000000 AND 999999999

THEN (dbo.fnMoneyToEnglish( @Number / 1000000))+' Million '+

dbo.fnMoneyToEnglish( @Number % 1000000)

WHEN @Number BETWEEN 1000000000 AND 999999999999

THEN (dbo.fnMoneyToEnglish( @Number / 1000000000))+' Billion '+

dbo.fnMoneyToEnglish( @Number % 1000000000)

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+' Dollars and '
SELECT @English = @English+
convert(varchar,convert(int,100*(@Money - @Number))) +' Cents'
if (@MinusFlag = 1)
begin
set @English = '( ' + @English + ' )'
end

END

RETURN (@English)

END

GO

Advertisements