Advent of Code 2023 in Oracle - Day 1 and 2

Advent of Code is a great yearly programming challenge. Each day (1st to 25th December), a new challenge arises that challenges your problem-solving and programming skills. I learn a lot every year, and it is a lot of fun.

I will do the challenges in Oracle (SQL and, where necessary, PL/SQL) this year again. You can find my solutions in this repository and, if I find the time, on this blog.

I strongly recommend checking out Advent of Code yourself before reading on. The challenges are clearly and in a Christmas theme. There is always a small sample input to test your solution and a bigger input to get the real result. When you solve the first challenge, you get a star, and a second, more difficult challenge appears.

I am doing the challenges on a local 23c free edition database. I am eager to explore some of the new features of 23c, so some of the syntax might not work on older versions. Furthermore, I will also use the Oracle APEX API packages.

I import the input data into a table that stores each line of the input as a row.

create table aoc_input (
  day      integer             not null -- 1-25
, key      varchar2(255 char)  not null -- 'SAMPLE1', 'INPUT' etc.
, line_no  integer             not null -- 1, 2, 3, ...
, line_str varchar2(4000 char) not null -- The actual line
, constraint aoc_input_pk primary key (day, key, line_no)
);

I then use SQL Developer to import the input data into the table. I use a bind for the input and paste the data from the website:

begin
  insert into aoc_input
    (day, key, line_no, line_str)
  with x as (
    select :bind as data from dual
  )
  select 1
       , 'INPUT' -- or something like 'SAMPLE1'
       , rownum
       , column_value
    from x
       , apex_string.split(x.data, chr(10));

  commit;
end;
/

You can read the challenge text here.

We can start by removing all non-numeric characters from the lines:

select line_no
       -- remove all non numeric characters
     , regexp_replace(line_str, '[^0-9]', '') as line
  from aoc_input
 where day = 1
   and key = '1'
 order by line_no
;
/*
   LINE_NO LINE
__________ _______
         1 9
         2 1
         3 37
         4 38
         5 38
*/

Next, we need to extract the first and last digits of each line. We can just use substr for that. With 1 we get the first, and with -1 the last character. Additionally, we need to concatenate both characters and convert the result to a number:

with only_nums as (
  select line_no
         -- remove all non numeric characters
       , regexp_replace(line_str, '[^0-9]', '') as line
    from aoc_input
   where day = 1
     and key = '1'
   order by line_no
)
select line_no
       -- concat first and last character, convert res to number
     , to_number(substr(line, 1, 1) || substr(line, -1, 1)) as row_res
  from only_nums
;
/*
   LINE_NO    ROW_RES
__________ __________
         1         99
         2         11
         3         37
         4         38
         5         38
*/

At last, we only have to sum up all the results:

with only_nums as (
  select line_no
         -- remove all non numeric characters
       , regexp_replace(line_str, '[^0-9]', '') as line
    from aoc_input
   where day = 1
     and key = '1'
   order by line_no
), row_res as (
  select line_no
         -- concat first and last character, convert res to number
       , to_number(substr(line, 1, 1) || substr(line, -1, 1)) as row_res
    from only_nums
)
-- sum up all row results
select sum(row_res) from row_res
;

I really struggled with this one. We have to convert the words of digits like one to the actual number 1. At first, I thought a simple replace would do the trick. But this does not work, as eighthree should actually be converted to 83. With the replace approach, we would replace the shared t and thus the second replace would not work (e.g. 8hree).

I was already at the end of my SQL skills and thought only PL/SQL could solve this. So I created a function that loops over each character of the input and looks to see if there is a digit. If this is true, I append the digit to a result string; otherwise, I append the character itself. This works, but is not very elegant.

CREATE OR REPLACE FUNCTION day1_replace_digits(pi_str VARCHAR2) RETURN VARCHAR2 IS
    l_temp_str   VARCHAR2(4000);
    l_replaced_str VARCHAR2(4000) := '';
BEGIN
    dbms_output.put_line(pi_str);
    for i in 1 .. length(pi_str)
    loop
      l_temp_str := substr(pi_str, i);

      case
        when l_temp_str like 'one%' then
          l_replaced_str := l_replaced_str || '1';
        when l_temp_str like 'two%' then
          l_replaced_str := l_replaced_str || '2';
        when l_temp_str like 'three%' then
          l_replaced_str := l_replaced_str || '3';
        when l_temp_str like 'four%' then
          l_replaced_str := l_replaced_str || '4';
        when l_temp_str like 'five%' then
          l_replaced_str := l_replaced_str || '5';
        when l_temp_str like 'six%' then
          l_replaced_str := l_replaced_str || '6';
        when l_temp_str like 'seven%' then
          l_replaced_str := l_replaced_str || '7';
        when l_temp_str like 'eight%' then
          l_replaced_str := l_replaced_str || '8';
        when l_temp_str like 'nine%' then
          l_replaced_str := l_replaced_str || '9';
        else
          l_replaced_str := l_replaced_str || substr(pi_str, i, 1);
      end case;

    end loop;

    dbms_output.put_line(l_replaced_str);
    RETURN l_replaced_str;
END day1_replace_digits;
/


-- Example
-- Input:  four5djlmjfive99eightonefour
-- Output: 4our5djlmj5ive998ight1ne4our

We can now apply the same logic as in star 1 to get our result:

with digit_str as (
  select line_no
       , day1_replace_digits(line_str) as line_str
       , line_str as orig
    from aoc_input
   where day = 1
     and key = '1'
), only_nums as (
  select line_no
         --remove all non-digits
       , regexp_replace(line_str, '[^0-9]', '') as line
       , line_str
       , orig
    from digit_str
   order by line_no
), row_res as (
  select line_no
       , to_number(substr(line, 1, 1) || substr(line, -1, 1)) as row_res
       , line_str
       , orig
    from only_nums
)
select sum(row_res) from row_res
;

You can read the challenge text here.

This challenge is a great example of how great SQL is for data analysis. I noticed pretty quickly that we want to get the highest count for each colored block of each line. So we will probably need the max function.

Additionally, we would like to get the counts for each color. Repeated text blocks scream regex to me. To catch each amount of green blocks in Game 1: 3 blue, 4 red; 1 red, 2 green, 6 blue; 20 green e.g. a regex like ([0-9]+) green will work. Notice how I use the brackets to capture the number.

Another trick to do this in a quick fashion is using apex_string.grep. This function returns the beautiful APEX_T_VARCHAR2 type to which I dedicated a whole blog post.

We can use this and the regex to capture the numbers:

with str as (
  select 'Game 1: 3 blue, 4 red; 1 red, 2 green, 6 blue; 20 green' as s
)
select apex_string.grep(s, '([0-9]+) green', 'i', '1') as arr
  from str
;
/*
ARR
__________
[2, 20]
*/

More interestingly, we can use the table operator to query the array. With that, we can just ask for the maximum value. The table thing returns the column column_value which we first need to convert to a number.

with str as (
  select 'Game 1: 3 blue, 4 red; 1 red, 2 green, 6 blue; 20 green' as s
)
select (select max(to_number(column_value)) from table(apex_string.grep(s, '([0-9]+) green', 'i', '1') ) ) as max
  from str
;
/*
   MAX
______
    20
*/

We can apply that pattern to every color to get the max count for each color:

select line_no
      , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) green', 'i', '1') ) ) as green
      , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) blue', 'i', '1') ) ) as blue
      , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) red', 'i', '1') ) ) as red
     , line_str
  from aoc_input
 where day = 2
   and key = 'INPUT'
 order by line_no
;
/*
   LINE_NO    GREEN    BLUE    RED LINE
__________ ________ _______ ______ ____________________________________
         1        2      15     12 Game 1: 1 green, 2 blue; 15 bl...
         2        9      19      6 Game 2: 5 green, 2 red, 18 blu...
         3       10       8     16 Game 3: 16 red, 10 green; 12 r...
         4       20       9      1 Game 4: 9 blue, 20 green; 1 re...
         5        4       1     10 Game 5: 3 green, 8 red; 1 blue...
*/

Next, we can just apply a where clause to filter out all the impossible games and sum the line_no to get our result:

with max_draws as (
  select line_no
       , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) green', 'i', '1') ) ) as green
       , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) blue', 'i', '1') ) ) as blue
       , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) red', 'i', '1') ) ) as red
       , line_str
       --, regexp_substr(line_str, '[0-9]+ green')
    from aoc_input
   where day = 2
     and key = 'INPUT'
   order by line_no
), possible_games as (
  select *
    from max_draws
   where red <= 12
     and green <= 13
     and blue <= 14
)
select sum(line_no)
from possible_games
;

Star 2 was no problem at all because of our work on Star 1. We only need to multiply the maximum values for each color and sum the results:

with max_draws as (
  select line_no
       , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) green', 'i', '1') ) ) as green
       , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) blue', 'i', '1') ) ) as blue
       , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) red', 'i', '1') ) ) as red
       , line_str
    from aoc_input
   where day = 2
     and key = 'INPUT'
   order by line_no
), power as (
  select max_draws.*, green * blue * red as pwr
  from max_draws
)
select sum(pwr) from power
;

If you find different or better solutions, please let me know in the comments. I am always eager to learn new things.

You can check out the full code on GitHub.