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_substr for parsing and regexp_count it’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 case statement 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;

You can find the solution to both parts on Oracle LiveSQL.


0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.