I have not had the opportunity to test these, all of which were posted by individuals to the SQL Server Central site. Review them, test them and say a quiet thank you to those generous people who posted them. Keep in mind that it is generally faster to have your front end do the conversion and not T-SQ"L
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udfn_NumberToWords]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'/*----------------------------------------------------------------------------------------------------------------------
Name: ctfn_NumberToWords
Version: 1.0
Date: 21-Feb-2007
Description: Takes an integer and converts it to words by storing the
integer into a table three digits at a time as hundreds, tens
and a factorial multiplier ''units''.
Returns a VARCHAR(2000) string containing the text of the
number, e.g.: ONE THOUSAND ONE HUNDRED AND TWENTY FOUR
Returns MINUS for a minus number and NULL if there is an error
Usage: SELECT dbo.ctfn_NumberToWords(INTEGER)
SELECT dbo.ctfn_NumberToWords(1787.55) + '' DOLLARS '' + dbo.ctfn_NumberToWords((1787.55 - CAST(1787.55 AS INT)) * 100) + '' CENTS.''
gives
ONE THOUSAND SEVEN HUNDRED AND EIGHTY SEVEN DOLLARS FIFTY FIVE CENTS.
David le Quesne
----------------------------------------------------------------------------------------------------------------------
History
1.0 DLQ 21-02-2007 RFC001428 Function created for ATPs
----------------------------------------------------------------------------------------------------------------------*/
CREATE FUNCTION [dbo].[udfn_NumberToWords] (@intNumberValue INTEGER)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @strNumberString VARCHAR(9)
DECLARE @strReturn VARCHAR(2000)
DECLARE @intUnits SMALLINT
-- Create table of number groups
DECLARE @tblNumberGroups TABLE (Units SMALLINT, Hundreds SMALLINT, Tens SMALLINT)
-- Handle errors and ''quick wins''
IF @intNumberValue IS NULL RETURN NULL
IF ISNUMERIC(@intNumberValue)=0 RETURN NULL
IF @intNumberValue = 0 RETURN ''ZERO''
IF @intNumberValue 0 OR (@intNumberValue/1000) >0
BEGIN
INSERT INTO @tblNumberGroups (Units, Hundreds, Tens) VALUES (@intUnits, (@intNumberValue % 1000)/100, (@intNumberValue % 1000) % 100 )
SELECT @intNumberValue = CAST (@intNumberValue / 1000 AS INTEGER)
SET @intUnits = @intUnits + 1
END
-- Remove last unit added
SET @intUnits = @intUnits-1
-- Concatenate text number by reading number groups in reverse order
SELECT @strReturn = ISNULL(@strReturn,'' '') +
ISNULL(
ISNULL((CASE Hundreds
WHEN 1 THEN ''ONE HUNDRED ''
WHEN 2 THEN ''TWO HUNDRED ''
WHEN 3 THEN ''THREE HUNDRED ''
WHEN 4 THEN ''FOUR HUNDRED ''
WHEN 5 THEN ''FIVE HUNDRED ''
WHEN 6 THEN ''SIX HUNDRED ''
WHEN 7 THEN ''SEVEN HUNDRED ''
WHEN 8 THEN ''EIGHT HUNDRED ''
WHEN 9 THEN ''NINE HUNDRED ''
END),'' '') +
CASE WHEN (Hundreds >0 OR Units 0 THEN '' AND '' ELSE '' '' END +
ISNULL((CASE Tens / 10
WHEN 2 THEN ''TWENTY ''
WHEN 3 THEN ''THIRTY ''
WHEN 4 THEN ''FORTY ''
WHEN 5 THEN ''FIFTY ''
WHEN 6 THEN ''SIXTY ''
WHEN 7 THEN ''SEVENTY ''
WHEN 8 THEN ''EIGHTY ''
WHEN 9 THEN ''NINETY ''
END),'' '') +
ISNULL((CASE Tens
WHEN 10 THEN ''TEN ''
WHEN 11 THEN ''ELEVEN ''
WHEN 12 THEN ''TWELVE ''
WHEN 13 THEN ''THIRTEEN ''
WHEN 14 THEN ''FOURTEEN ''
WHEN 15 THEN ''FIFTEEN ''
WHEN 16 THEN ''SIXTEEN ''
WHEN 17 THEN ''SEVENTEEN ''
WHEN 18 THEN ''EIGHTEEN ''
WHEN 19 THEN ''NINETEEN ''
END),'' '') +
COALESCE(
CASE WHEN Tens %10 =1 AND Tens / 10 1 THEN ''ONE '' END,
CASE WHEN Tens %10 =2 AND Tens / 10 1 THEN ''TWO '' END,
CASE WHEN Tens %10 =3 AND Tens / 10 1 THEN ''THREE '' END,
CASE WHEN Tens %10 =4 AND Tens / 10 1 THEN ''FOUR '' END,
CASE WHEN Tens %10 =5 AND Tens / 10 1 THEN ''FIVE '' END,
CASE WHEN Tens %10 =6 AND Tens / 10 1 THEN ''SIX '' END,
CASE WHEN Tens %10 =7 AND Tens / 10 1 THEN ''SEVEN '' END,
CASE WHEN Tens %10 =8 AND Tens / 10 1 THEN ''EIGHT '' END,
CASE WHEN Tens %10 =9 AND Tens / 10 1 THEN ''NINE '' END,
'' '')+
COALESCE(
CASE WHEN Units=1 AND (Hundreds>0 OR Tens>0) THEN ''THOUSAND '' END,
CASE WHEN Units=2 AND (Hundreds>0 OR Tens>0) THEN ''MILLION '' END,
CASE WHEN Units=3 AND (Hundreds>0 OR Tens>0) THEN ''BILLION '' END,
CASE WHEN Units=4 AND (Hundreds>0 OR Tens>0) THEN ''TRILLION '' END,
'' '')
,'' '')
FROM @tblNumberGroups
ORDER BY units DESC
-- Get rid of all the spaces
WHILE CHARINDEX('' '', @strReturn)>0
BEGIN
SET @strReturn = REPLACE(@strReturn,'' '','' '')
END
SET @strReturn = LTRIM(RTRIM(@strReturn))
RETURN @strReturn
END
'
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSpellNumber]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnSpellNumber] ( @number varchar(20) )
RETURNS VARCHAR(200)
AS
--For debugging: declare @number varchar(20) set @number = ''192.1''
BEGIN
-- This is for use outside of a function: DECLARE @debug bit SET @debug = 0
DECLARE @result varchar(200), @word varchar(100)
DECLARE @i int, @intpart varchar(20), @decpart varchar(20)
SET @word = LTRIM(RTRIM(@number))
-- Check for a bad number, e.g., one with embedded spaces
IF ISNUMERIC(@word) = 0 RETURN ''<>''
SET @i = CHARINDEX(''.'', @word)
-- Remove trailing zeroes for any decimal portion
IF @i > 0 -- Number contains a decimal point
BEGIN
WHILE RIGHT(@word,1) = ''0'' SET @word = LEFT(@word,LEN(@word)-1)
IF @word = '''' SET @word = ''0''
END
-- Insert a decimal point at the end if none was specified
IF @i = 0 -- No decimal point
BEGIN
SET @word = @number + ''.''
SET @i = CHARINDEX(''.'', @word)
END
SET @intpart = LEFT(@word,@i-1)-- Extract the integer part of the number if any
IF LEN(@intpart) > 0
SET @result = master.dbo.fnSpellInteger(CAST(@intpart AS int))
ELSE
SET @result = ''''
-- Extract the decimal portion of the number
SET @decpart = RIGHT(@word,LEN(@word)-@i)-- @i is position of decimal point
IF LEN(@decpart) > 0
BEGIN
IF @result = ''Zero''
SET @result = ''''
ELSE IF @result ''''
SET @result = @result + '' and ''
SET @result = @result + master.dbo.fnSpellInteger(@decpart) +
CASE LEN(@decpart)
WHEN 0 THEN ''''
WHEN 1 THEN '' Tenths''
WHEN 2 THEN '' Hundredths''
WHEN 3 THEN '' One-Thousandths''
WHEN 4 THEN '' Ten-Thousandths''
WHEN 5 THEN '' One-Hundred-Thousandths''
WHEN 6 THEN '' One-Millionths''
WHEN 7 THEN '' Ten-Millionths''
WHEN 8 THEN '' One-Hundred-Millionths''
WHEN 9 THEN '' One-Billionths''
END
-- Check for a valid plural
IF @decpart = 1 SET @result = LEFT(@result, LEN(@result)-1)-- Remove last "s" for just 1
END
-- This is for use outside of a function: if @debug = 1 select @word as ''@word'', @i as ''@i'', @intpart as ''@intpart'', @decpart as ''@decpart'', @result as ''@result''
RETURN @result
END
'
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSpellInteger]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnSpellInteger] ( @number int )
RETURNS VARCHAR(100)
AS
BEGIN
-- For debugging outside of the UDF: DECLARE @debug bit SET @debug = 0
DECLARE @result VARCHAR(100), @word VARCHAR(100), @group VARCHAR(100)
DECLARE @i int, @j-2 int, @m int, @digit VARCHAR(2), @cn VARCHAR(20)
IF @number = 0 RETURN ''Zero''
SELECT @result = '''', @word = '''', @group = ''''
SET @cn = @number
SET @cn = REPLACE(@cn,'','','''')
SET @m = LEN(@cn) % 3
IF @m > 0 SET @cn = REPLICATE(''0'',3-@m) + @cn-- Left pad with zeroes to a multiple of 3
SET @i = 1
SET @j-2 = LEN(@cn)-@i+1
SET @m = @i % 3
WHILE @i <= LEN(@cn)
BEGIN
-- @i is 1 origin index into numeric string while @m = @i modulo 3
-- If the middle digit of each group of 3 is a ''1'' then this is a ''Ten'' or a ''...teen''
IF @m = 2 AND SUBSTRING(@cn,@i,1) = ''1''
BEGIN
SET @digit = SUBSTRING(@cn,@i,2)
-- Skip rightmost digit of 3 if processing teens
SET @i = @i + 1
END
ELSE
SET @digit = SUBSTRING(@cn,@i,1)
SET @word =
CASE
WHEN @m = 0 THEN-- Rightmost digit of group of 3
CASE @digit
WHEN ''0'' THEN ''''
WHEN ''1'' THEN ''One''
WHEN ''2'' THEN ''Two''
WHEN ''3'' THEN ''Three''
WHEN ''4'' THEN ''Four''
WHEN ''5'' THEN ''Five''
WHEN ''6'' THEN ''Six''
WHEN ''7'' THEN ''Seven''
WHEN ''8'' THEN ''Eight''
WHEN ''9'' THEN ''Nine''
END +
CASE
WHEN (@group '''' OR @digit ''0'') AND (@j+2) / 3 = 2 THEN '' Thousand''
WHEN (@group '''' OR @digit ''0'') AND (@j+2) / 3 = 3 THEN '' Million''
WHEN (@group '''' OR @digit ''0'') AND (@j+2) / 3 = 4 THEN '' Billion''
ELSE ''''
END
WHEN LEN(@digit) = 2 THEN-- Special case when middle digit is a ''1''
CASE @digit
WHEN ''10'' THEN ''Ten''
WHEN ''11'' THEN ''Eleven''
WHEN ''12'' THEN ''Twelve''
WHEN ''13'' THEN ''Thirteen''
WHEN ''14'' THEN ''Fourteen''
WHEN ''15'' THEN ''Fifteen''
WHEN ''16'' THEN ''Sixteen''
WHEN ''17'' THEN ''Seventeen''
WHEN ''18'' THEN ''Eighteen''
WHEN ''19'' THEN ''Nineteen''
END +
CASE
WHEN (@group '''' OR @digit ''00'') AND (@j+2) / 3 = 2 THEN '' Thousand''
WHEN (@group '''' OR @digit ''00'') AND (@j+2) / 3 = 3 THEN '' Million''
WHEN (@group '''' OR @digit ''00'') AND (@j+2) / 3 = 4 THEN '' Billion''
ELSE ''''
END
WHEN @m = 2 THEN-- Middle digit of group of 3
CASE @digit
WHEN ''2'' THEN ''Twenty''
WHEN ''3'' THEN ''Thirty''
WHEN ''4'' THEN ''Forty''
WHEN ''5'' THEN ''Fifty''
WHEN ''6'' THEN ''Sixty''
WHEN ''7'' THEN ''Seventy''
WHEN ''8'' THEN ''Eighty''
WHEN ''9'' THEN ''Ninety''
ELSE ''''
END
WHEN @m = 1 THEN-- Leftmost digit of group of 3
CASE @digit
WHEN ''0'' THEN ''''
WHEN ''1'' THEN ''One''
WHEN ''2'' THEN ''Two''
WHEN ''3'' THEN ''Three''
WHEN ''4'' THEN ''Four''
WHEN ''5'' THEN ''Five''
WHEN ''6'' THEN ''Six''
WHEN ''7'' THEN ''Seven''
WHEN ''8'' THEN ''Eight''
WHEN ''9'' THEN ''Nine''
END +
CASE WHEN @digit ''0'' THEN '' Hundred'' ELSE '''' END
END
SET @group = @group + RTRIM(@word)-- Group value
IF @word ''''
BEGIN
DECLARE @prefix VARCHAR(20)
IF CHARINDEX('' '',@word) > 0 SET @prefix = LEFT(@word,CHARINDEX('' '',@word)) ELSE SET @prefix = @word
IF RIGHT(@result,2) = ''ty'' AND @prefix IN (''One'',''Two'',''Three'',''Four'',''Five'',''Six'',''Seven'',''Eight'',''Nine'')
SET @result = @result + ''-'' + LTRIM(@word)
ELSE
SET @result = @result + '' '' + LTRIM(@word)
END
-- The following needs to be outside of a UDF to work:
--IF @debug = 1 SELECT @cn as ''Number'', @i as ''@i'', @j-2 as ''@j'', @m as ''@m'', @digit as ''@digit'', CAST(replace(@group,'' '',''`'') AS CHAR(30)) as ''@group'', @word as ''@word'', @result as ''@result''
SET @i = @i + 1
SET @j-2 = LEN(@cn)-@i+1
SET @m = @i % 3
IF @m = 1 SET @group = ''''-- Clear group value when starting a new one
END
IF @result = '''' SET @result = ''0''
RETURN LTRIM(@Result)
END
'
END