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 ')

Rate

Share

Share

Rate