Post date: Jun 11, 2014 12:28:59 AM
"Import a big text file of comma-separated first names. Translate each name into a score where As are 1 points, Bs are 2 points, etc, up until Zs at 26 points. Then multiply by the name's position in the list in alphabetical order. Sum up all these numbers."
This one is tedious and annoying, but very simple.
-- We need a function to find the value for a name. Do a simple lookup for each letter and sum the result.
-- This could have been done with a table rather than a loop, except that it would be even messier.
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 -- While
RETURN @value
END
GO
-- Load the text file into a table
CREATE TABLE #Names (name nvarchar(16))
BULK INSERT #Names FROM 'D:\names.txt' WITH (ROWTERMINATOR = ',')
UPDATE #Names
SET name = REPLACE(name,'"','')
-- Get the answer.
; WITH [Values] (value, place) AS
(
SELECT dbo.GetNameValue(name)
, ROW_NUMBER() OVER (ORDER BY name)
FROM #Names
)
SELECT SUM(value * place)
FROM [Values]
GO
DROP TABLE #Names
DROP FUNCTION dbo.GetNameValue
GO