SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF 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'))BEGINexecute 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 BEGIN SET @strReturn=''MINUS '' SET @intNumberValue=ABS(@intNumberValue) END SET @intUnits =0 -- Populate table of number groups WHILE (@intNumberValue % 1000) > 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<@intUnits) AND Tens > 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 @strReturnEND' END
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF 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'))BEGINexecute 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 ''<< NOT A NUMBER >>'' 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 @resultEND' END
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF 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'))BEGINexecute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[fnSpellInteger] ( @number int )RETURNS VARCHAR(100)ASBEGIN -- 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 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 = 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 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 = 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
