In my previous post, I introduced you to the Advent of Code programming challenge, and explained how I’m going through the days, solving both parts of each puzzle. Continuing on, today we have day 5. Since you can only see this if you are up to this level in the challenge, let me explain it to you. In part one of this puzzle, Santa needs to figure out how many of the strings in his text file are “nice” strings. The text file to use can be found here. The string is a “nice” string if it contains at least 3 vowels, has any letter that appears twice consecutively, and it does not contain the strings “ab”, “cd”, “pq” or “xy”.

Once again, this list exceeds the limitations of my favorite string splitter function, so I’m going to use the alternate manner I used earlier to load it:

The list (in the @InputText variable) actually contains 1000 lines. I’m declaring a few variables to determine how many rows there are, and the number of characters per row. In the next phase, I’m once again using a virtual tally table to split this string. I then check each line for whether it contains any of those conditions by comparing the length of the string to the string after the characters to search for have been removed, and finally I get a count of the number that meet the nice criteria.

After looking at this list, Santa realizes that the nice criteria isn’t valid, so some new criteria comes out. A “nice” string now must contain a pair of letters that appears in a row twice without overlapping, and a letter that repeats in the string with exactly one letter between those two repeating letters. In SQL, this is just pattern matching, and these can be performed with the LIKE operator. The hard part here is getting all of the valid pattern matching strings. I do this with a virtual tally table to get all of the letters of the alphabet, and then performing a set of queries to get all of these combinations (including the necessary wildcard characters to make the complete pattern to match). They are inserted into a table along with a number to identify which set of matching criteria that they belong to:

At this point, all that is needed is to join the tables of lines to this table twice (once for each matching condition) using the LIKE operator, and to get a count of the number of distinct strings that are nice:

The distinct is in there since the matching patterns can produce multiple rows for a single line.