Advent of Code 2022 Day 3 with some SQL + a little PL/pgSQL

program_03.sql

drop table if exists rucksacks;
create table rucksacks (
  id serial primary key,
  content text
);

copy rucksacks (content) from '/Users/cjw/Documents/aoc22/input_03.txt'
with csv;

create or replace function letter_value(letter char) returns integer as $$
begin
  return case
         when ascii(letter) between ascii('a') and ascii('z') then ascii(letter) - 96
         when ascii(letter) between ascii('A') and ascii('Z') then ascii(letter) - 38
         end;
end;
$$ language plpgsql;

with parts as
  (select
    id,
    substring(content from 1 for length(content)/2) as first,
    substring(content from length(content)/2+1) as second
  from rucksacks
  ), shared_letters as (
    select distinct
      id, unnest(regexp_split_to_array(parts.first, '')) as letter
      from parts
    intersect
    select distinct
      id, unnest(regexp_split_to_array(parts.second, '')) as letter
      from parts
  )
select sum(letter_value(letter)) from shared_letters;