Sometimes you need to step back, walk through various coding adventures, and rebalance yourself. Thankfully, I have found one set that I like – and has been running for several years. And it’s all about our actual season – the Advent season. (If you’re reading this long after it’s published, you can also figure out when it’s being published.) If you’re not sure what the Advent season is or why it needs its special calendar, I would suggest reading one of my favorite sites for all things historical – An Historian About Town.
One of the many things I enjoy about #AdventOfCode is that you are not given a specific language to code in. You’re just asked for a final result. Because of that, I am doing the exercises in both Excel and SQL Server. Yes, you read that correctly – Excel and SQL Server.
I am going to stick to just the SQL Server portion on this blog, but if you would like to see the Excel version of these questions, please feel free to leave a comment or shoot me an email, and I’ll be happy to respond and answer any questions you may have.
To enjoy these puzzles, you will need to go to the Official Advent of Code website, sign up for their leaderboards and whatnot if you choose to, and then continue to 2023 and Day 1. Today, we’ll start with Day 1 – since it is the first of our programming puzzles and work our way up from there…
We’re asked – given a string – to find the first (and last) number in that string. We are then to concatenate them, add them all up, and provide the result. It should be pretty simple, but let’s see…
The first thing we need to do is load the data into our trusty SQL Server. I’ve gone through many methods previously, so be sure to check those out here.
I loaded all of the data into the table aocDay1. I even placed it into one field called strPhraseGiven. To get the first number from the string, I chose to use the following code:
SUBSTRING(strPhraseGiven, PATINDEX('%[0-9]%', strPhraseGiven), 1)I chose to use PATINDEX because it allows for regex style searching mostly. As always, feel free to use any other function and experiment to see what works for you and what doesn’t.
After PATINDEX returned the position of the string, I was able to use the SUBSTRING function to get the exact value I needed – the actual number – by using the 1 to return the value, since all of our numbers are one character long.
To find the last number in the string, we actually do almost the same function as above – except we have to reverse the string itself. Thankfully, SQL Server has a function called REVERSE. (And here you was thinking you’d never actually get to use that function IRL!)
The code I ended up with for this section is:
SUBSTRING(REVERSE(strPhraseGiven), PATINDEX('%[0-9]%', REVERSE(strPhraseGiven)), 1)Now, pay attention to the fact that both fields are reversed this time. That is because the PATINDEX function gives us the position of the string provided – which is reversed. If we tried to find the number in the non-reversed string using the position returned from the reversed string, we would end up with data we did not expect.
Perfect… Now we have all of the numbers we need. We just need to combine them. Reviewing the request, we are told to combine the first digit and the last digit (in that order) to form a single two-digit number.
Some of you may think this is just adding the two together! As we say here in the South:
This is where we should be using the CONCAT function to treat them as strings and push the two together. Once that is complete, we can quickly sum them all up! You should come up with an answer in the 50K range.
Overall, a nice little exercise to keep you entertained and – hopefully- learn a few new tricks and functions. With that, there is a part 2, which we’ll walk through tomorrow and go from there! Until then my friends – Work on getting better with exercises!