• 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

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]