Hopefully, by now, you’ve read my post Walking Through the Advent of Code Day 3. After looking through it, you’re probably thinking… “What have I gotten myself into? Is SQL the way to go with all of this? Can I back out now and learn a programming language that AoC can be done in “easily”?”
For Part 2 of the Day 3 smorgasbord of goodness, we can actually use most of the code that we used earlier in Day 3 Part 1.
The #Day3Numbers table will be the exact same – since it’s just providing us the product numbers that are possible to work with.
The difference lies with the Special Characters temp table.
The only special character we actually have to care about this time is the lovely “*” character – since that is the character that denotes that we have an actual gear.
For this to happen, we can eliminate the actual RegEx portion of our pattern and change it to the easily recognizable – ‘%*%’ for the Special Character table.
For the rest of this question, we must check if there are 2 – and only 2 – part numbers close to the symbol. So this time, we are going to do a take-off of what we did last time – we’re going to use two fields to show which numbers we’re close to.
I added 2 fields to the #Day3SpecChars temp table with the following command:
ALTER TABLE #Day3SpecChars
ADD NearProduct1 int NULL,
NearProduct2 int NULL;But, wait, if we do this, we’re going to assume that we have 2 part numbers for each and that’s not quite what we’re asked for. Let’s add a third column – NearProduct3 – so that if we fill this value, then we can automatically fail that line and move on.
Also notice, that in case you’ve missed it before, you can add multiple columns to a table in one command. I’ve heard multiple people say that it can’t be done – at least in SQL Server it can!
From here, you can work through the multiple scenarios you worked through before – above, below, left, right, and diagonal. But, you do have to be careful of putting data into fields that already have data in them. For example, you shouldn’t put data into NearProduct1 if you’ve already worked through a scenario that puts data into that row in #Day3SpecChars. Thankfully, we can use the WHERE clause “WHERE NearProduct1 IS NULL” to “fork” the update into 2 different runs – one that has data in NearProduct1 and one that does not – so the number of queries has gone significantly up. (And for some of you thinking ahead, you will need to add code for when there is data in NearProduct2 – and you just want to put something in NearProduct3 to make sure that you don’t use that data.)
Once you have run through all of the scenarios, you will want to grab data only from those that have data in NearProduct1 and NearProduct2 – but not NearProduct3 – and multiply those together and sum them up.
SELECT SUM(NearProduct1 * NearProduct2)
FROM #Day3SpecChars
WHERE ISNULL(NearProduct3, 0) = 0;Please note that your number is very high – at least when compared to the number that we’ve been providing to AoC previously.
And with that, we’ve come to the conclusion of yet another puzzle of #AdventOfCode. A little bit of convolution – is that a word? – but not awful. We at least got rid of some of the original RegEx from the Day3Part1 code.
From here on, we’ll have nothing but smooth sailing… at least until we start with Day 4 of #AdventOfCode! So join me tomorrow for yet another coding adventure!