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;
You can find the solution to both parts on Oracle LiveSQL.
Top comments (0)