Post date: Jun 04, 2014 12:31:10 AM
"How many letters (not space or punctuation) are in all the English-language numbers between one and one thousand? Include 'and' after the hundreds place in 'one hundred and twelve,' etc."
I really hate this one.
CREATE TABLE #Numbers (i varchar(4) PRIMARY KEY NOT NULL, word varchar(50) NULL)
; WITH Integers (i) AS
(
SELECT 0
UNION ALL
SELECT i + 1
FROM Integers
WHERE i < 999
)
INSERT INTO #Numbers (i)
SELECT i + 1
FROM Integers
OPTION (MAXRECURSION 1000)
/* Now to do a bunch of nasty word replacements. */
-- Last digit.
UPDATE #Numbers
SET word = CASE RIGHT(i,1)
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
WHERE RIGHT(i,1) IN ('1','2','3','4','5','6','7','8','9')
-- Special numbers in English. This will update some that I just updated, but it doesn't waste a lot of time.
UPDATE #Numbers
SET word = CASE RIGHT(i,2)
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
WHERE RIGHT(i,2) IN ('10','11','12','13','14','15','16','17','18','19')
-- Tens place. Note that this is appending to the existing value
UPDATE #Numbers
SET word = CASE LEFT(RIGHT(i,2),1)
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(word,'')
WHERE LEFT(RIGHT(i,2),1) IN ('2','3','4','5','6','7','8','9')
AND LEN(i) > 1
-- Project Euler wants the word 'and' in 'n hundred and twelve.'
-- This will attempt to update the ones ending in 00, which don't need an 'and.' But it will set them to NULL, which is what they were already, because NULL + something = NULL.
UPDATE #Numbers
SET word = 'and' + word
WHERE LEN(i) = 3
-- Hundreds place
UPDATE #Numbers
SET word = CASE LEFT(i,1)
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 + 'hundred' + ISNULL(word,'')
WHERE LEN(i) = 3
-- The last number
UPDATE #Numbers
SET word = 'onethousand'
WHERE i = '1000'
SELECT SUM(len(word))
FROM #Numbers
DROP TABLE #Numbers