My Sunday night hobby for the past couple of months is to try and programatically solve the NPR Sunday Puzzle.   Each Sunday morning on Weekend Edition Sunday Will Shortz and Liane Hansen offer a puzzle.  I have been a fan of this for well over ten years, but recently started using it as a way to have fun programming.  Most of the recent puzzles have involved words of a certain type (such as job titles or animals).  Since I don't have a complete database of words in a particular category, my resolution of the Sunday puzzle has not been complete, (i.e. to the point of saying "The Answer is: x").   After 5 or 6 weeks of getting a range of possible answers they finally gave me a puzzle that could easily be solved.

I missed last weeks puzzle because I didn't wake up in time, but remembered on Saturday night just in time to solve it before waking up to the answer tomorrow morning.

Last weeks puzzle is:

From Ed Pegg, Jr., who runs the Web site mathpuzzle.com: The numbers 2, 4, 6 and 30 are the first four numbers whose names lack the letter "E." What is the 23rd number whose name lacks an "E?"

I had wondered what the significance of the 23rd number was, if I was more quick witted, that alone should have been enough of a clue to solve the first puzzle of the new year.

Instead, I decided to use T-SQL as the language for this, in part because I think that the .Net Framework has a function that does what I did in the function below, taking the fun out of it.

So, here is the T-SQL code.  I know it is choppy and clunky, and the variable names don't make sense, but it works really well (for numbers up to 4 digits long) and was very easy to write.  In addition, it is a nice example of recursion, which makes me feel good after reading this post by Joel Spolsky.  I noticed as I pasted the code into this post that I have done a number of implicit variable conversions, this code would not hold up well in a code review, but it got the job done.

create function fn_NumberToText(@value int) returns varchar(100)

declare @name varchar(100), @valueText varchar(9),

@Left1 char(1), @RightOrdinal char(1), @RightPair char(2),

@RightTrio varchar(3), @Left2 char(2)

set @valueText = Convert(varchar(5), @value)

if LEN(@valueText) = 4

set @Left1 = Left(@valueText, 1)

set @name = dbo.fn_NumberToText(@Left1) + ' ' + 'Thousand'

set @RightTrio = RIGHT(@valueText, 3)

set @name = @name + ' ' + dbo.fn_NumberToText(@RightTrio)

if LEN(@valueText) = 3

set @Left1 = LEFT(@valueText, 1)

set @name = dbo.fn_NumberToText(@Left1) + ' ' + 'Hundred'

set @RightPair = Convert(varchar(2), Convert(int, right(@valueText, 2)))

set @name = @name + ' ' + dbo.fn_NumberToText(@RightPair)

if LEN(@valueText) = 2 and LEFT(@valueText, 1) <> '1'

set @Left1 = LEFT(@valueText, 1)

set @RightOrdinal = Right(@valueText, 1)

select @name = case @Left1

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

select @name = @name + ' ' + dbo.fn_NumberToText(@RightOrdinal)

if LEN(@valueText) = 2 and LEFT(@valueText, 1) = '1'

select @name = case @value

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'

if LEN(Convert(varchar(4), @value)) = 1

select @name = case @value

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 0 then ''

end

return @name

declare @iter int, @match int, @text varchar(100)

set @iter = 1

set @match = 0

while @match < 23

set @text = dbo.fn_NumberToText(@iter)

if charindex('e', @text) = 0

BEGIN

set @match = @match + 1

if @match = 23

print 'The Answer is: ' + @Text

-- TESTING CODE -- print Convert(char(2), @match) + ': ' + @text

END

set @iter = @iter + 1