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
