Advent of Code 2022 in Oracle - Day 3

Advent of Code is a cool yearly programming challenge. Each day from 1st to 25th December a new challenge appears. You can solve it in any programming language you like. After deepening my Go skills last year I will do this in Oracle (so PL/SQL and SQL) this year.

I try to blog about my solutions but I won’t make it every day or give up after a while as they get more difficult and time-consuming. You can find my solutions for this year in this repository.

I really suggest you check Advent of Code out yourself as it is a lot of fun and you will improve your problem-solving skills.

The challenges are clearly phrased with a simple example you can do in your head. You then get a way bigger generated and user-specific input and have to solve the challenge with that. You have to enter a solution in an input field and get a star for each day you solved. There is also a second challenge every day where you have to adapt some part of your algorithm to solve it.

You can find the challenge here.

Today’s challenge is quite more complex than yesterday’s.

We get a pretty random-looking input that consists of random letters both lower and uppercase:

vJrwpWtwJgWrhcsFMMfFFhFp
jqHRNqRjqzjGDLGLrsFMfFZSrLrFZsSL
PmmdzqPrVvPwwTWBwg
wMqvLMZHhHMvwLHjbvcjnnSBnvTQFn
ttgJtRGJQctTZtZT
CrZsJsPPZsGzwwsLwLmpwMDw

Each line represents one elf. Each letter represents some kind of item in the backpack of an elf.

The first task is to find the item of each elf, that is present in the first and second half of the input. We then need to translate this letter to a priority number (a-z = 1-26, A-Z = 27-52) and sum up all these.

Today I had to fall back to PL/SQL again after I did yesterday’s challenge purely in SQL.

At first, I went ahead and created a table to store the backpack data of each elf:

create table aoc_backpacks (
  id number primary key
, line  varchar2(1000)
, half1 varchar2(500)
, half2 varchar2(500)
) inmemory;

The second table holds the char present in both halves and the priority number:

create table aoc_duplicate_chars (
  line     number primary key
, dup_char varchar2(1 char)
, prio     number
) inmemory;

To fill the first table we split the input by line and then split it into halves with substr:

insert into aoc_backpacks
with data as (
  select rownum as line_no
       , column_value as line
       , length(column_value) as line_len
                 -- split by newline
    from table ( select apex_string.split(input)
                   from aoc_input
                  where year = 2022 and day = 3
               )
)
select line_no as id
     , line
     , substr(line, 1, line_len / 2) as half1
     , substr(line, line_len / 2 + 1,  line_len / 2) as half2
  from data
;

We can now go ahead and find the duplicate chars in each half. For that we need to loop through every char of the first half and check if it is present in the second one:

declare
  type t_backpacks is table of aoc_backpacks%rowtype;
  l_backpacks t_backpacks;

  l_len   pls_integer;
  l_char  varchar2(1 char);
  l_count pls_integer;
begin
  select *
    bulk collect into l_backpacks
    from aoc_backpacks
  ;

  for i in 1 .. l_backpacks.count loop
    l_len := length(l_backpacks(i).half1);

    -- loop through every char of half1
    for j in 1 .. l_len loop
      l_char := substr(l_backpacks(i).half1, j, 1);

      -- count occurrences of char in half2
      l_count := regexp_count(l_backpacks(i).half2, l_char);

      if l_count > 0 then
        dbms_output.put_line(
          apex_string.format('(%0) Char "%1" found in each half.', i, l_char)
        );

        insert into aoc_duplicate_chars (line, dup_char) values (i, l_char);

        -- we only need to find the first match
        exit;

      end if;
    end loop;
  end loop;
end;

Note that we did not assign a priority number yet. We can do that in a single update statement afterward. I did not want to manually assign a letter to all 52 numbers so I used the ascii function. It returns an ASCII integer code for a given character. It does not match the numbers we need but we can easily calculate them with a simple formula as they increase by 1 for each letter:

update aoc_duplicate_chars
   set prio  = case when lower(dup_char) = dup_char then
                 ascii(dup_char) - 96  -- when lower char, subtract 96
               else
                 ascii(dup_char) - 38  -- when upper char, subtract 38
               end
;

Lastly, we only need to sum the priorities of all duplicate chars:

select sum(prio) as sum
  from aoc_duplicate_chars
;

You can check out the full solution here.

Part 2 is a bit more complex. The backpacks are not halved anymore instead we need to find the letter appearing for every elf in groups of 3. So lines 1-3 are a group, 4-6, and so on.

We only need to modify the PL/SQL code. Instead of processing every line, we are looking at each group in a single loop now. We then need to find the longest line in the group and loop through every char of that line and check if it also appears in the other lines of the group:

declare
  type t_backpacks is table of aoc_backpacks%rowtype;
  l_backpacks t_backpacks;

  l_longest_line aoc_backpacks.line%type;
  l_len   pls_integer;
  l_char  varchar2(1 char);

  l_idx_to_check1 pls_integer;
  l_found1        boolean;
  l_idx_to_check2 pls_integer;
  l_found2        boolean;
begin
  select *
    bulk collect into l_backpacks
    from aoc_backpacks
  ;

  for i in 1 .. l_backpacks.count loop
   -- only process every third line
    if mod(i, 3) != 0 then
      continue;
    end if;

    dbms_output.put_line('Processing line ' || i);

    l_longest_line := l_backpacks(i).line;
    l_len := length(l_backpacks(i).line);
    l_idx_to_check1 := i - 1;
    l_idx_to_check2 := i - 2;

    -- check if line in other group is longer
    if length(l_backpacks(i - 1).line) > l_len then
      l_longest_line := l_backpacks(i - 1).line;
      l_len := length(l_backpacks(i - 1).line);
      l_idx_to_check1 := i;
      l_idx_to_check2 := i - 2;
    end if;

    if length(l_backpacks(i - 2).line) > l_len then
      l_longest_line := l_backpacks(i - 2).line;
      l_len := length(l_backpacks(i - 2).line);
      l_idx_to_check1 := i;
      l_idx_to_check2 := i - 1;
    end if;

    -- dbms_output.put_line('Str1: ' || l_longest_line);
    -- dbms_output.put_line('Str2: ' || l_backpacks(l_idx_to_check1).line || ' - ' || l_idx_to_check1);
    -- dbms_output.put_line('Str3: ' || l_backpacks(l_idx_to_check2).line || ' - ' || l_idx_to_check2);

    -- loop every char of the longest line
    for j in 1 .. l_len loop
      l_char := substr(l_longest_line, j, 1);

      -- count occurrences of char in two other lines
      l_found1 := regexp_count(l_backpacks(l_idx_to_check1).line, l_char) > 0;
      l_found2 := regexp_count(l_backpacks(l_idx_to_check2).line, l_char) > 0;

      if l_found1 and l_found2 then
        dbms_output.put_line(
          apex_string.format('(%0) Char "%1" found in every line.', i / 3, l_char)
        );

        insert into aoc_triplet_chars (line, dup_char) values (i, l_char);

        -- we only need to find the first match
        exit;

      end if;
    end loop;
  end loop;
end;

At last, we need again to assign a priority number and sum them up. You can check out the full solution here.

I really enjoyed this challenge. I can imagine that there are many different approaches to solving it. I would love to see your solutions.