Advent Of Code 2020 in SQL – Day 2: “Password Philosophy”

#adventofcode #sql

Today’s challenge is to analyze a given list of passwords towards the provided policy.

The policy of part 1 is that the given character must occur in the password inside a given threshold – sounds like we could use some regex.

The biggest challenge – again – was to get the input data into the database (I want to avoid setting up tables if I can). sys.odcivarchar2list seems like a good choice, but the input has 1000 entries and the constructor of odcivarchar2list only supports 999 items.

Luckily, we got the UNION ALL operator 🙂

select column_value line from table(sys.odcivarchar2list(
      '9-10 m: mmmmnxmmmwm',
      '6-8 w: wpwwhxnv',
      ... -- up to 999 items
      ))
    union all
    select column_value from table(sys.odcivarchar2list('17-18 b: jnlntbblbbqbkqmbbb'))

To make the implementation more readable, I use a lot of WITH-selects. Thanks to regexp_substrfor parsing and regexp_countit’s straight forward to solve the puzzle.

with
  input as (
    select column_value line 
    from table(sys.odcivarchar2list(...))
  ),
  regex_pattern as (
    select '([0-9]+)-([0-9]+) ([a-z]): ([a-z]+)' pattern 
      from dual
  ),
  parsed_input as (
    select
      regexp_replace(line, regex.pattern, '\1') min_occurence,
      regexp_replace(line, regex.pattern, '\2') max_occurence,
      regexp_replace(line, regex.pattern, '\3') search_char,
      regexp_replace(line, regex.pattern, '\4') password,
      line original_line
      from input, regex_pattern regex
  )
select
  count(*)
  from parsed_input
  where regexp_count(password, search_char) 
    between min_occurence and max_occurence
;

Part 2 now changes the policy from analyzing the occurence to check whether exactly one of the chars at the two given positions in the string match the searched character.

Good thing we have the casestatement in SQL:

with
  input as (
    select column_value line 
    from table(sys.odcivarchar2list(...))
  ),
  regex_pattern as (
    select '([0-9]+)-([0-9]+) ([a-z]): ([a-z]+)' pattern 
      from dual
  ),
  parsed_input as (
    select
      regexp_replace(line, regex.pattern, '\1') first_position,
      regexp_replace(line, regex.pattern, '\2') second_position,
      regexp_replace(line, regex.pattern, '\3') search_char,
      regexp_replace(line, regex.pattern, '\4') password,
      line original_line
      from input, regex_pattern regex
  ),
  positions_analyzed as (
    select
      password,
      case
        when substr(password, first_position, 1) = search_char 
        then
          1
        else
          0
        end first_pos_found,
      case
        when substr(password, second_position, 1) = search_char 
        then
          1
        else
          0
        end second_pos_found
      from parsed_input
  )
select
  count(*)
  from positions_analyzed
  where (first_pos_found+second_pos_found) = 1;