# Advent of Code 2022 in Oracle - Day 3

## Advent of Code

#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.

## Challenge 1

#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:

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.

## Solving challenge 1

#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:

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

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

:

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:

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:

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

You can check out the full solution here.

## Challenge 2

#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.

## Solving challenge 2

#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:

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.