0
ALTER FUNCTION [dbo].[NumberToWords](@Number DECIMAL(18, 2))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Words NVARCHAR(MAX)

SELECT @Words = 
    COALESCE(@Words + ' ', '') +
    CASE
        WHEN @Number = 0 THEN ''
        WHEN @Number BETWEEN 1 AND 99 THEN 
            CASE @Number
                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'
                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'
                WHEN 20 THEN 'Twenty'
                WHEN 21 THEN 'Twenty-one'
                WHEN 22 THEN 'Twenty-two'
                WHEN 23 THEN 'Twenty-three'
                WHEN 24 THEN 'Twenty-four'
                WHEN 25 THEN 'Twenty-five'
                WHEN 26 THEN 'Twenty-six'
                WHEN 27 THEN 'Twenty-seven'
                WHEN 28 THEN 'Twenty-eight'
                WHEN 29 THEN 'Twenty-nine'
                WHEN 30 THEN 'Thirty'
                WHEN 31 THEN 'Thirty-one'
                WHEN 32 THEN 'Thirty-two'
                WHEN 33 THEN 'Thirty-three'
                WHEN 34 THEN 'Thirty-four'
                WHEN 35 THEN 'Thirty-five'
                WHEN 36 THEN 'Thirty-six'
                WHEN 37 THEN 'Thirty-seven'
                WHEN 38 THEN 'Thirty-eight'
                WHEN 39 THEN 'Thirty-nine'
                WHEN 40 THEN 'Forty'
                WHEN 41 THEN 'Forty-one'
                WHEN 42 THEN 'Forty-two'
                WHEN 43 THEN 'Forty-three'
                WHEN 44 THEN 'Forty-four'
                WHEN 45 THEN 'Forty-five'
                WHEN 46 THEN 'Forty-six'
                WHEN 47 THEN 'Forty-seven'
                WHEN 48 THEN 'Forty-eight'
                WHEN 49 THEN 'Forty-nine'
                WHEN 50 THEN 'Fifty'
                WHEN 51 THEN 'Fifty-one'
                WHEN 52 THEN 'Fifty-two'
                WHEN 53 THEN 'Fifty-three'
                WHEN 54 THEN 'Fifty-four'
                WHEN 55 THEN 'Fifty-five'
                WHEN 56 THEN 'Fifty-six'
                WHEN 57 THEN 'Fifty-seven'
                WHEN 58 THEN 'Fifty-eight'
                WHEN 59 THEN 'Fifty-nine'
                WHEN 60 THEN 'Sixty'
                WHEN 61 THEN 'Sixty-one'
                WHEN 62 THEN 'Sixty-two'
                WHEN 63 THEN 'Sixty-three'
                WHEN 64 THEN 'Sixty-four'
                WHEN 65 THEN 'Sixty-five'
                WHEN 66 THEN 'Sixty-six'
                WHEN 67 THEN 'Sixty-seven'
                WHEN 68 THEN 'Sixty-eight'
                WHEN 69 THEN 'Sixty-nine'
                WHEN 70 THEN 'Seventy'
                WHEN 71 THEN 'Seventy-one'
                WHEN 72 THEN 'Seventy-two'
                WHEN 73 THEN 'Seventy-three'
                WHEN 74 THEN 'Seventy-four'
                WHEN 75 THEN 'Seventy-five'
                WHEN 76 THEN 'Seventy-six'
                WHEN 77 THEN 'Seventy-seven'
                WHEN 78 THEN 'Seventy-eight'
                WHEN 79 THEN 'Seventy-nine'
                WHEN 80 THEN 'Eighty'
                WHEN 81 THEN 'Eighty-one'
                WHEN 82 THEN 'Eighty-two'
                WHEN 83 THEN 'Eighty-three'
                WHEN 84 THEN 'Eighty-four'
                WHEN 85 THEN 'Eighty-five'
                WHEN 86 THEN 'Eighty-six'
                WHEN 87 THEN 'Eighty-seven'
                WHEN 88 THEN 'Eighty-eight'
                WHEN 89 THEN 'Eighty-nine'
                WHEN 90 THEN 'Ninety'
                WHEN 91 THEN 'Ninety-one'
                WHEN 92 THEN 'Ninety-two'
                WHEN 93 THEN 'Ninety-three'
                WHEN 94 THEN 'Ninety-four'
                WHEN 95 THEN 'Ninety-five'
                WHEN 96 THEN 'Ninety-six'
                WHEN 97 THEN 'Ninety-seven'
                WHEN 98 THEN 'Ninety-eight'
                WHEN 99 THEN 'Ninety-nine'
            END
        WHEN @Number BETWEEN 100 AND 999 THEN 
            dbo.NumberToWords(@Number / 100) + ' Hundred ' + dbo.NumberToWords(@Number % 100)
        WHEN @Number BETWEEN 1000 AND 999999 THEN 
            dbo.NumberToWords(@Number / 1000) + ' Thousand ' + dbo.NumberToWords(@Number % 1000)
        WHEN @Number BETWEEN 1000000 AND 999999999 THEN 
            dbo.NumberToWords(@Number / 1000000) + ' Million ' + dbo.NumberToWords(@Number % 1000000)
        ELSE 'Value too large'
    END
        
RETURN @Words
END

To call it i'm using

DECLARE @Number DECIMAL(18, 2) = 1004
SELECT dbo.NumberToWords(@Number) AS Words

CASE ERRORS:

  • 20 ✅ Twenty
  • 100 ✅ One Hundred
  • 101 ❌ NULL
  • 102 ❌ NULL
  • 105 ❌ NULL
  • 200 ✅ Two hundred
  • 204 ❌ NULL
  • 1000 ✅ One Thousand
  • 1004 ✅ One Thousand Four
  • 1005 ❌ NULL
  • 1020 ❌ NULL

I thought it was because it couldn't recurse more than one time but it handles One Thousand Four no problems.

1
  • When you're doing things like dividing @Number by 100 or 1000, etc., and passing that as the parameter to the function, then you are really going to potentially be passing values like 1.1, 1.2, etc. - and of course, you've only got case statements deal with whole numbers between 1 and 99
    – Craig
    Feb 9 at 3:07

1 Answer 1

2

Modify the 100/1000/1000000 "WHEN" portions of the SELECT statement to be like this, and you should achieve your desired result.

WHEN @Number BETWEEN 100 AND 999 THEN 
    dbo.NumberToWords((@Number - @Number % 100) / 100) + ' Hundred ' + dbo.NumberToWords(@Number % 100)
WHEN @Number BETWEEN 1000 AND 999999 THEN 
    dbo.NumberToWords((@Number - @Number % 1000) / 1000) + ' Thousand ' + dbo.NumberToWords(@Number % 1000)
WHEN @Number BETWEEN 1000000 AND 999999999 THEN 
    dbo.NumberToWords((@Number - @Number % 1000000) / 1000000) + ' Million ' + dbo.NumberToWords(@Number % 1000000)

In this way, you are passing whole numbers in your sub-calls to the function, rather than fractional values that aren't catered for in the "1 to 99" case

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.