Walking Through the Advent of Code Day 1 Part 2

Hopefully, you’ve read Walking Through the Advent of Code Day 1 – otherwise, this post is going to be like walking into a 4″x4″ post – rather jolting.

I also hope that you’re making progress on the #AdventOfCode series. I’m sure some of you are on the Overall Leaderboard. Be sure to check on the list for yours truly… Way down the list…

Today, I want to review part 2 of Day 1 of the Advent of Code series. Hopefully, everyone was able to complete part 1 with no troubles, or at least understood what I did to get there.

For part 2, they added a slight wrinkle to the part 1 puzzle. They spell out the numbers into actual words! How do you find them as well as find the numbers? Well, my friend, let’s go through that process, shall we?

The first part of the process is exactly the same as step 1. Create a table that has the string, FirstNumberShown, and LastNumberShown. I actually added here 2 more columns showing the position of each of these values.

I also created a secondary temp table called ListOfValues that had two columns – a string to search for and the numeric value of it.

CREATE TABLE ListOfValues 
( TheValue varchar(10) NOT NULL,
	NumericValue int NOT NULL);

INSERT INTO ListOfValues
SELECT 'zero',0		UNION ALL
SELECT 'one', 1		UNION ALL
SELECT 'two', 2		UNION ALL
SELECT 'three',3	UNION ALL
SELECT 'four',4		UNION ALL
SELECT 'five',5		UNION ALL
SELECT 'six', 6		UNION ALL
SELECT 'seven',7	UNION ALL
SELECT 'eight',8	UNION ALL
SELECT 'nine',9;

And this is where I may grind the gears of a few long time database people. I looped through the data finding the strings that were earlier in the string than the position listed and populated that.

SELECT TOP 1 @ThePhrase = TheValue, @NumberfiedPhrase = NumericValue  FROM ListOfValues

;with cte AS (
     SELECT *, SUBSTRING(strPhraseGiven, PATINDEX('%' + @ThePhrase + '%', strPhraseGiven), len(@ThePhrase)) AS PhraseToUpdateTo,
		PATINDEX('%' + @ThePhrase + '%', strPhraseGiven) AS PosOfPhrase
     FROM aocDay1Part2
     WHERE PATINDEX('%' + @ThePhrase + '%', strPhraseGiven) > 0	
     AND PATINDEX('%' + @ThePhrase + '%', strPhraseGiven) < FirstNumberPos)
UPDATE cte
SET FirstNumber = @NumberfiedPhrase,
    FirstNumberPos = PosOfPhrase

Notice that the cte only gets the data for those that need to be updated if it fits in the following 2 categories:

1. If it actually has the new phrase in it
2. If the position is less than what is currently in the FirstNumberPos

This allows us to easily update only those items in the cte without even using a JOIN back to the table.

We do the same thing for the Last Number by using the REVERSE function.

;with cte AS (
	SELECT *, SUBSTRING(REVERSE(strPhraseGiven), PATINDEX('%' + REVERSE(@ThePhrase) + '%', REVERSE(strPhraseGiven)), len(@ThePhrase)) AS PhraseToUpdateTo,
		PATINDEX('%' + REVERSE(@ThePhrase) + '%', REVERSE(strPhraseGiven)) AS PosOfPhrase
	FROM aocDay1Part2
	WHERE PATINDEX('%' + REVERSE(@ThePhrase) + '%', REVERSE(strPhraseGiven)) > 0
	AND PATINDEX('%' + REVERSE(@ThePhrase) + '%', REVERSE(strPhraseGiven)) < LastNumberPos)
UPDATE cte
SET LastNumber = @NumberfiedPhrase,
	LastNumberPos = PosOfPhrase

From this point forward, it’s the same as Part 1 – concatenate the First and Last Numbers and add them all up.

Notice that this is a different number than what we had for Part 1, so be careful of that.

I’m sure some of you are wondering why I chose the while loop? First reason being that I’m just not a fan of cursors. Could I have used one? Yes – since I have an extremely limited amount of data that I’ll be looping through here.

Second, after years of working with data in chunks, it’s almost easier to do it this way for me.

Am I sure there is a way to do it without a loop?

Now, if you came up with a solution that doesn’t use a loop and finishes in less than 1 second (because that’s how long this version takes with 1,000 data points), I’ll be happy to discuss it with you.

Until next time when we talk thru Day 2 of the #AdventOfCode.