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)