Convert a number to words
This is a handy function that converts a number (integer) into its word format. This might be handy if you need to print a check formatted number in words. Currently it supports an integer but it would be almost nothing to convert to support a bigint or decimal.
The components are
1) a table named PlaceValue that holds certain number parts and place values
2) A function named FormatTriplet() that formats a 3-digit grouping
3) The actual function named NumberToText() that formats a number.
this is rather slow because long numbers (millions) may take up to 9 hits to the data base. If you're doing lots of these, i recommend creating a view (preferably indexed) with this value as one of the columns. Or create a denormalized field and store this value in it.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PlaceValue]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PlaceValue]
GO
CREATE TABLE [dbo].[PlaceValue] (
[ValueID] [int] NOT NULL ,
[ValueText] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PlaceValue] WITH NOCHECK ADD
CONSTRAINT [PK_PlaceValue] PRIMARY KEY CLUSTERED
(
[ValueID]
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udfFormatTriplet]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udfFormatTriplet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udfNumberToText]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udfNumberToText]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE function dbo.udfFormatTriplet(@triplet as int)
returns varchar(255)
as
/*****************************************
*
* copyright 2002 (c): don frazier
* all rights reserved.
*
* Permission is granted to use this function
* provided no fee is charge for its use
* or distribution.
*
* This notice must remain intact in all
* executable and published copies.
*
* Convert a number from 1-999 to its text
* counterpart.
*
*****************************************/begin
if @triplet > 999
OR @Triplet < 0
begin
return 'Invalid value: ' + cast(@Triplet as varchar(200))
end
declare @Hundreds int
, @Tens int
, @Units int
, @TripletValue varchar(255)
set @Hundreds = @Triplet / 100
set @Tens = @Triplet - (@Hundreds * 100)
if @Tens > 20
begin
set @Units = @Tens % 10
set @Tens = @Tens - @Units
end
else
begin
set @Units = 0
end
if @Hundreds > 0
begin
select @TripletValue = ValueText + 'Hundred '
from PlaceValue
where ValueID=@Hundreds
end
else
begin
set @Tripletvalue = ''
end
select @TripletValue = @TripletValue + ValueText
from PlaceValue
where ValueID=@Tens
select @TripletValue = @TripletValue + ValueText
from PlaceValue
where ValueID=@Units
return @Tripletvalue -- + ' ' + cast(@Hundreds as varchar(5)) + '-' + cast(@Tens as varchar(5))+ '-' + cast(@Units as varchar(5))
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE function dbo.udfNumberToText(@Value as int)
returns varchar(255)
as
/*****************************************
*
* copyright 2002 (c): don frazier
* all rights reserved.
*
* Permission is granted to use this function
* provided no fee is charge for its use
* or distribution.
*
* This notice must remain intact in all
* executable and published copies.
*
* Convert an integer number from 1-2Billion to its text
* counterpart.
*
*****************************************/begin
declare @Triplet int
, @Group int
, @Words varchar(255)
, @Sign int
set @Value = abs(@Value)
if @Value = 0 -- special case
return 'Zero'
set @Words = ''
set @Group = 0
while @Value > 0
begin
set @Triplet = @Value % 1000
set @Value = @Value / 1000
if @Triplet > 0
select @Words = dbo.udfFormatTriplet(@Triplet) + ValueText + @Words
from PlaceValue
where ValueID = @Group
set @Group = @Group - 1
end
return @Words
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
insert placevalue(ValueID, ValueText) values( -11, 'Decillion ')
insert placevalue(ValueID, ValueText) values( -10, 'Nonillion ')
insert placevalue(ValueID, ValueText) values( -9, 'Octillion ')
insert placevalue(ValueID, ValueText) values( -8, 'Septillion ')
insert placevalue(ValueID, ValueText) values( -7, 'Sextillion ')
insert placevalue(ValueID, ValueText) values( -6, 'Quintillion ')
insert placevalue(ValueID, ValueText) values( -5, 'Quadrillion ')
insert placevalue(ValueID, ValueText) values( -4, 'Trillion ')
insert placevalue(ValueID, ValueText) values( -3, 'Billion ')
insert placevalue(ValueID, ValueText) values( -2, 'Million ')
insert placevalue(ValueID, ValueText) values( -1, 'Thousand ')
insert placevalue(ValueID, ValueText) values( 0, '')
insert placevalue(ValueID, ValueText) values( 1, 'One ')
insert placevalue(ValueID, ValueText) values( 2, 'Two ')
insert placevalue(ValueID, ValueText) values( 3, 'Three ')
insert placevalue(ValueID, ValueText) values( 4, 'Four ')
insert placevalue(ValueID, ValueText) values( 5, 'Five ')
insert placevalue(ValueID, ValueText) values( 6, 'Six ')
insert placevalue(ValueID, ValueText) values( 7, 'Seven ')
insert placevalue(ValueID, ValueText) values( 8, 'Eight ')
insert placevalue(ValueID, ValueText) values( 9, 'Nine ')
insert placevalue(ValueID, ValueText) values( 10, 'Ten ')
insert placevalue(ValueID, ValueText) values( 11, 'Eleven ')
insert placevalue(ValueID, ValueText) values( 12, 'Twelve ')
insert placevalue(ValueID, ValueText) values( 13, 'Thirteen ')
insert placevalue(ValueID, ValueText) values( 14, 'Fourteen ')
insert placevalue(ValueID, ValueText) values( 15, 'Fifteen ')
insert placevalue(ValueID, ValueText) values( 16, 'Sixteen ')
insert placevalue(ValueID, ValueText) values( 17, 'Seventeen ')
insert placevalue(ValueID, ValueText) values( 18, 'Eighteen ')
insert placevalue(ValueID, ValueText) values( 19, 'Nineteen ')
insert placevalue(ValueID, ValueText) values( 20, 'Twenty ')
insert placevalue(ValueID, ValueText) values( 30, 'Thirty ')
insert placevalue(ValueID, ValueText) values( 40, 'Forty ')
insert placevalue(ValueID, ValueText) values( 50, 'Fifty ')
insert placevalue(ValueID, ValueText) values( 60, 'Sixty ')
insert placevalue(ValueID, ValueText) values( 70, 'Seventy ')
insert placevalue(ValueID, ValueText) values( 80, 'Eighty ')
insert placevalue(ValueID, ValueText) values( 90, 'Ninety ') 
