# Mastermind Solver by Kim Berg Hansen

• Script Name Mastermind Solver by Kim Berg Hansen
• Description Kim Berg Hansen, author of many SQL quizzes and all-around SQL guru, couldn't resist the challenge of provided a SQL-only Mastermind solution generator, after two PL/SQL scripts were made available.
• Category SQL General
• Contributor Steven Feuerstein (Oracle)
• Created Thursday May 11, 2017
• Statement 1
``````with
/*
Automatic Solution Generator for
PL/SQL Challenge's version of Mastermind

Author: Kim Berg Hansen (player name: kibeha)

Requires Oracle Database 11g Release 1 or higher.

Permission granted by author to make it available to others to
use and modify to their own purposes.

Usage:

In the guess_string subquery, put a commaseparated string
of guesses. Each guess with 4 digits followed by N and P clues.
Spaces are ignored. Order of N and P is ignored.

Examples:

with guess_string as (
select '7 6 5 4 P N, 7 1 2 6 P N, 4 1 5 2 N N' str from dual

with guess_string as (
select '4321NN,6437NN' str from dual

with guess_string as (
select '2461NP, 2734NP, 2153NP' str from dual

*/
guess_string as (
select '7 6 5 4 P N, 7 1 2 6 P N, 4 1 5 2 N N' str from dual
), guesses as (
select guess_id
, to_number(substr(str,1,1)) digit1
, to_number(substr(str,2,1)) digit2
, to_number(substr(str,3,1)) digit3
, to_number(substr(str,4,1)) digit4
, regexp_count(str,'P') num_p_clues
, regexp_count(str,'N') num_n_clues
, count(*) over () guess_count
from (
select level guess_id
, regexp_substr(str,'(.*?)(,|\$)',1,level,null,1) str
from (
select upper(replace(str,' ')) str
from guess_string
)
connect by level <= regexp_count(str,',') + 1
)
), digits as (
select level digit
from dual
connect by level <= 7
), possibles as (
select rownum solution_id
, d1.digit digit1
, d2.digit digit2
, d3.digit digit3
, d4.digit digit4
from digits d1
join digits d2
on d2.digit != d1.digit
join digits d3
on d3.digit != d1.digit
and d3.digit != d2.digit
join digits d4
on d4.digit != d1.digit
and d4.digit != d2.digit
and d4.digit != d3.digit
)
select max(digit1) digit1
, max(digit2) digit2
, max(digit3) digit3
, max(digit4) digit4
from (
select p.solution_id
, p.digit1
, p.digit2
, p.digit3
, p.digit4
, count(*) over (partition by p.solution_id) solution_count
, g.guess_count
from possibles p
cross join guesses g
where case p.digit1 when g.digit1 then 1 else 0 end
+ case p.digit2 when g.digit2 then 1 else 0 end
+ case p.digit3 when g.digit3 then 1 else 0 end
+ case p.digit4 when g.digit4 then 1 else 0 end = g.num_p_clues
and case when p.digit1 in (g.digit2, g.digit3, g.digit4) then 1 else 0 end
+ case when p.digit2 in (g.digit1, g.digit3, g.digit4) then 1 else 0 end
+ case when p.digit3 in (g.digit1, g.digit2, g.digit4) then 1 else 0 end
+ case when p.digit4 in (g.digit1, g.digit2, g.digit3) then 1 else 0 end = g.num_n_clues
)
where solution_count = guess_count
group by solution_id ``````
DIGIT1DIGIT2DIGIT3DIGIT4
3625
3724
5623
6324
7235
7243
7315
7341
7413
7431
7513
7531

12 rows selected.