Post date: Sep 04, 2014 12:28:33 AM
"Using a text file containing common English words, and converting each letter in each word to a simple numeric value (A = 1, B = 2, etc), and summing those values, how many of the sums you have calculated match a value in the sequence 1/2 * n * ( n + 1) where n is a positive integer?"
I feel ripped off that this problem in no way related to the answer to the ultimate question of life, the universe, and everything. Aside from that, it is pretty simple. I take a simple function that runs a case statement on each letter, sums up the values, and returns a result. Simply fill a result set with members of this 'triangle' sequence and join between the words and the sequence, using the function.
We have actually already calculated the triangle sequence in a different problem, using a more drawn out method, but we're dealing with such tiny numbers that the formula, even though more efficient, doesn't provide any noticeable improvement in speed. But the code is cleaner.
CREATE FUNCTION dbo.GetNameValue (@name varchar(16))
RETURNS int
AS
BEGIN
DECLARE @value int = 0
WHILE 1 = 1
BEGIN
IF @name = '' BREAK
SELECT @value = @value + CASE LEFT(@name,1)
WHEN 'A' THEN 1 WHEN 'B' THEN 2 WHEN 'C' THEN 3 WHEN 'D' THEN 4 WHEN 'E' THEN 5
WHEN 'F' THEN 6 WHEN 'G' THEN 7 WHEN 'H' THEN 8 WHEN 'I' THEN 9 WHEN 'J' THEN 10
WHEN 'K' THEN 11 WHEN 'L' THEN 12 WHEN 'M' THEN 13 WHEN 'N' THEN 14 WHEN 'O' THEN 15
WHEN 'P' THEN 16 WHEN 'Q' THEN 17 WHEN 'R' THEN 18 WHEN 'S' THEN 19 WHEN 'T' THEN 20
WHEN 'U' THEN 21 WHEN 'V' THEN 22 WHEN 'W' THEN 23 WHEN 'X' THEN 24 WHEN 'Y' THEN 25
WHEN 'Z' THEN 26 END
SET @name = STUFF(@name,1,1,'')
END
RETURN @value
END
GO
-- Bulk load the comma-separated list of words.
CREATE TABLE #Words (word nvarchar(16))
BULK INSERT #Words FROM 'D:\words.txt' WITH (ROWTERMINATOR = ',')
UPDATE #Words
SET word = REPLACE(word,'"','')
-- The longest words are 14 characters. If one were all Zs, then that would be a value of 364. That's such a tiny series that we don't need to fill a table. A simple CTE will do the trick.
; WITH Integers (i) AS
(
SELECT 1
UNION ALL
SELECT i + 1
FROM Integers
WHERE i < 99
),
Triangle (i, value) AS
(
SELECT i, 0.5 * i * (i + 1)
FROM Integers
WHERE 0.5 * i * (i + 1) <= 364
GROUP BY i
)
SELECT COUNT(*)
FROM #Words w
INNER JOIN Triangle t
ON t.value = dbo.GetNameValue(w.word)
DROP TABLE #Words
GO
DROP FUNCTION dbo.GetNameValue
GO