Skip to content

Instantly share code, notes, and snippets.

@thomasbilk
Last active December 8, 2018 02:33
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save thomasbilk/9888299 to your computer and use it in GitHub Desktop.
Save thomasbilk/9888299 to your computer and use it in GitHub Desktop.
Solve Sudoku in SQL
WITH RECURSIVE x( s, ind ) AS
( SELECT sud, position( '.' IN sud )
FROM (SELECT '91.7......326.9.8...7.8.9...86.3.17.3.......6.51.2.84...9.5.3...2.3.149......2.61'::text
AS sud) xx
UNION ALL
SELECT substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )
, position('.' IN repeat('x',ind) || substr( s, ind + 1 ) )
FROM x
, (SELECT gs::text AS z FROM generate_series(1,9) gs) z
WHERE ind > 0
AND NOT EXISTS ( SELECT NULL
FROM generate_series(1,9) lp
WHERE z.z = substr( s, ( (ind - 1 ) / 9 ) * 9 + lp, 1 )
OR z.z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
OR z.z = substr( s, mod( ( ( ind - 1 ) / 3 ), 3 ) * 3
+ ( ( ind - 1 ) / 27 ) * 27 + lp
+ ( ( lp - 1 ) / 3 ) * 6
, 1 )
)
)
SELECT s
FROM x
WHERE ind = 0;
-- solves this sudoku
-- 91. 7.. ...
-- .32 6.9 .8.
-- ..7 .8. 9..
-- .86 .3. 17.
-- 3.. ... ..6
-- .51 .2. 84.
-- ..9 .5. 3..
-- .2. 3.1 49.
-- ... ..2 .61
WITH RECURSIVE
input(sud) AS (
VALUES('91.7......326.9.8...7.8.9...86.3.17.3.......6.51.2.84...9.5.3...2.3.149......2.61')
),
digits(z, lp) AS (
VALUES('1', 1)
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
x(s, ind) AS (
SELECT sud, instr(sud, '.') FROM input
UNION ALL
SELECT
substr(s, 1, ind-1) || z || substr(s, ind+1),
instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
FROM x, digits AS z
WHERE ind>0
AND NOT EXISTS (
SELECT 1
FROM digits AS lp
WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+ ((ind-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6, 1)
)
)
SELECT s FROM x WHERE ind=0;
-- solves this sudoku
-- 91. 7.. ...
-- .32 6.9 .8.
-- ..7 .8. 9..
-- .86 .3. 17.
-- 3.. ... ..6
-- .51 .2. 84.
-- ..9 .5. 3..
-- .2. 3.1 49.
-- ... ..2 .61
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment