# Mastermind Solver by James Su

• Script Name Mastermind Solver by James Su
• Description James put together this SQL query to solve Mastermind-like puzzles offered on the PL/SQL Challenge (plsqlchallenge.oracle.com)
• Category SQL General
• Contributor Steven Feuerstein (Oracle)
• Created Thursday May 11, 2017
• Statement 1
``````WITH guess_string
/*
Automatic Solution Generator for
PL/SQL Challenge's version of Mastermind (plsqlchallenge.oracle.com)

Author: James Su

Requires Oracle Database 11g Release 2 or higher. (10g-compatible version follows)

Permission granted by author to publish this code make it available to others to
use and modify to their own purposes.
*/
AS (SELECT '7654PN,7126PN,4152NN' g_str FROM DUAL),
g
AS (    SELECT LEVEL lvl,
UPPER (REPLACE (REGEXP_SUBSTR (g_str,
'[^,]+',
1,
LEVEL),
' '))
g_str
FROM guess_string
CONNECT BY LEVEL <= REGEXP_COUNT (g_str, ',') + 1),
t (s, lvl)
AS (    SELECT REPLACE (SYS_CONNECT_BY_PATH (n, '/'), '/'), 1
FROM (    SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 7)
WHERE LEVEL = 4
CONNECT BY NOCYCLE n <> PRIOR n
UNION ALL
SELECT t.s, t.lvl + 1
FROM t, g
WHERE     t.lvl = g.lvl
AND   4
- NVL (
LENGTH (
TRANSLATE (t.s, '@' || g.g_str, '@')),
0) = LENGTH (SUBSTR (g.g_str, 5))
AND   DECODE (SUBSTR (t.s, 1, 1),
SUBSTR (g.g_str, 1, 1), 1,
0)
+ DECODE (SUBSTR (t.s, 2, 1),
SUBSTR (g.g_str, 2, 1), 1,
0)
+ DECODE (SUBSTR (t.s, 3, 1),
SUBSTR (g.g_str, 3, 1), 1,
0)
+ DECODE (SUBSTR (t.s, 4, 1),
SUBSTR (g.g_str, 4, 1), 1,
0) = REGEXP_COUNT (g.g_str, 'P'))
SELECT t.s
FROM t
WHERE lvl =
(SELECT REGEXP_COUNT (g_str, ',') + 2
FROM guess_string) ``````
S
3625
3724
5623
6324
7235
7243
7315
7341
7413
7431
7513
7531

12 rows selected.