Solving a normal 9x9 sudoku takes more nesting levels than SQL-server allows. But it might suffice for the 6x6 solver.
Install on a local database only
CREATE TABLE [dbo].[sudokutable](
[sudoku] [char](81) NULL,
[sudoku6] [char](36) NULL
)
CREATE PROCEDURE [dbo].[sudoku6]
(@grid Char(36),
@result Char(36) output,
@suc6 TinyInt output)
AS
BEGIN
declare @r0 int = 0;
declare @c0 int = 0;
declare @r2 int = 0;
declare @c2 int = 0;
declare @n int = 0;
declare @num int = 1;
declare @fnd TinyInt = 0;
declare @r1 int = 1;
declare @c1 int = 1;
declare @r1c int = 0;
declare @c1c int = 0;
declare @gridTmp Char(36);
set @gridTmp = @grid;
set @suc6 = 0;
while @r0 < 6
begin
set @c0 = 0;
while @c0 < 6
begin
if substring(@gridTmp,(@r0)*6+@c0+1,1)='0'
begin
set @n = 0;
while @n < 6
begin
set @n = @n+1;
set @num = @n;
set @fnd = 0;
/*rows*/
set @r2 = 0;
while @r2 < 6
begin
if @r2<>@r0 and substring(@gridTmp,(@r2)*6+@c0+1,1)=@num
begin
set @fnd = 1;
break;
end
set @r2 = @r2+1;
end
if(@fnd=1) continue;
/*columns*/
set @c2 = 0;
while @c2 < 6
begin
if @c2<>@c0 and substring(@gridTmp,(@r0)*6+@c2+1,1)=@num
begin
set @fnd = 1;
break;
end
set @c2 = @c2+1;
end
if(@fnd=1) continue;
/*boxes*/
set @r1 = FLOOR(@r0/2)*2; /*0,1=0 2,3=2 4,5=4*/
set @c1 = FLOOR(@c0/3)*3;
set @r1c = @r1;
while @r1c < @r1+2
begin
set @c1c = @c1;
while @c1c < @c1+3
begin
if (@r0<>@r1c or @c0<>@c1c) and substring(@gridTmp,(@r1c)*6+@c1c+1,1)=@num
begin
set @fnd = 1;
break;
end
set @c1c = @c1c+1;
end
if @fnd=1 break;
set @r1c = @r1c+1;
end
if(@fnd=1) continue;
/*try it out*/
set @gridTmp = CONCAT(substring(@gridTmp,1,@r0*6+@c0),@num,substring(@gridTmp,@r0*6+@c0+2,36));
exec sudoku6 @grid = @gridTmp, @result = @gridTmp, @suc6 = @fnd
if @fnd=0
begin
set @gridTmp = @grid;
set @suc6 = 0;
end
else
begin
set @suc6 = @fnd;
set @result = @gridTmp;
INSERT INTO sudokutable(sudoku6) VALUES(@gridTmp);
return;
end
end
return;
end
set @c0 = @c0+1;
end
set @r0=@r0+1;
end
if charindex('0',@gridTmp)>0
begin
set @suc6 = 0;
set @result = '';
end
else
begin
set @suc6 = 1;
set @result = @gridTmp;
INSERT INTO sudokutable(sudoku6) VALUES(@gridTmp);
end
END
Note the indices are "one" based, not zero based. The solver uses string manipulations, since arrays are not available afaik.
DELETE FROM [sudokutable]
GO
DECLARE @return_value int,
@result char(36),
@suc6 tinyint
EXEC @return_value = [dbo].[sudoku6]
@grid = N'200140134652060000015030503061041000',
@result = @result OUTPUT,
@suc6 = @suc6 OUTPUT
SELECT @result as N'@result',
@suc6 as N'@suc6'
SELECT 'Return Value' = @return_value
SELECT TOP 1000 [sudoku6] FROM [sudokutable]
GO
Both SQL-server and mySql didn't cope with this. But with a partly solved sudoku I could show that the solver would work.
CREATE PROCEDURE [dbo].[sudoku]
(@grid Char(81),
@result Char(81) output,
@suc6 TinyInt output)
AS
BEGIN
declare @r0 int = 0;
declare @c0 int = 0;
declare @r2 int = 0;
declare @c2 int = 0;
declare @n int = 0;
declare @num int = 1;
declare @fnd TinyInt = 0;
declare @r1 int = 1;
declare @c1 int = 1;
declare @r1c int = 0;
declare @c1c int = 0;
declare @gridTmp Char(81);
set @gridTmp = @grid;
set @suc6 = 0;
while @r0 < 9
begin
set @c0 = 0;
while @c0 < 9
begin
if substring(@gridTmp,(@r0)*9+@c0+1,1)='0'
begin
set @n = 0;
while @n < 9
begin
set @n = @n+1;
set @num = @n;
set @fnd = 0;
/*rows*/
set @r2 = 0;
while @r2 < 9
begin
if @r2<>@r0 and substring(@gridTmp,(@r2)*9+@c0+1,1)=@num
begin
set @fnd = 1;
break;
end
set @r2 = @r2+1;
end
if(@fnd=1) continue;
/*columns*/
set @c2 = 0;
while @c2 < 9
begin
if @c2<>@c0 and substring(@gridTmp,(@r0)*9+@c2+1,1)=@num
begin
set @fnd = 1;
break;
end
set @c2 = @c2+1;
end
if(@fnd=1) continue;
/*boxes*/
set @r1 = FLOOR(@r0/3)*3; /*0,1,2 =0 3,4,5=3 6,7,8=6*/
set @c1 = FLOOR(@c0/3)*3;
set @r1c = @r1;
while @r1c < @r1+3
begin
set @c1c = @c1;
while @c1c < @c1+3
begin
if (@r0<>@r1c or @c0<>@c1c) and substring(@gridTmp,(@r1c)*9+@c1c+1,1)=@num
begin
set @fnd = 1;
break;
end
set @c1c = @c1c+1;
end
if @fnd=1 break;
set @r1c = @r1c+1;
end
if(@fnd=1) continue;
/*try it out*/
set @gridTmp = CONCAT(substring(@gridTmp,1,@r0*9+@c0),@num,substring(@gridTmp,@r0*9+@c0+2,81));
exec sudoku @grid = @gridTmp, @result = @gridTmp, @suc6 = @fnd
if @fnd=0
begin
set @gridTmp = @grid;
set @suc6 = 0;
end
else
begin
set @suc6 = @fnd;
set @result = @gridTmp;
INSERT INTO sudokutable(sudoku) VALUES(@gridTmp);
return;
end
end
return;
end
set @c0 = @c0+1;
end
set @r0=@r0+1;
end
if charindex('0',@gridTmp)>0
begin
set @suc6 = 0;
set @result = '';
end
else
begin
set @suc6 = 1;
set @result = @gridTmp;
INSERT INTO sudokutable(sudoku) VALUES(@gridTmp);
end
END
Main difference is the 9 instead of 6 gridsize, and the starting rows for the boxes. Other Column name in the Table.
DELETE FROM [sudokutable]
GO
DECLARE @return_value int,
@result char(81),
@suc6 tinyint
EXEC @return_value = [dbo].[sudoku]
--000000030050607004008903760000000520000216000042000000087302100200108090030000000
--796481235351627984428953761613894527579216348842735619987342156265178493134569872
@grid = N'796481235351627984428953761613894520000216000042000000087302100200108090030000000',
@result = @result OUTPUT,
@suc6 = @suc6 OUTPUT
SELECT @result as N'@result',
@suc6 as N'@suc6'
SELECT 'Return Value' = @return_value
SELECT TOP 1000 [sudoku] FROM [sudokutable]
GO
Note the half solved sudoku entered.
Note the syntax differences. Procedure looks the same but the result is a bit different.
CREATE TABLE `sudokutable` (
`sudoku` CHAR(81) NULL DEFAULT NULL
)
CREATE PROCEDURE `sudoku`(
IN `grid` char(81),
OUT `result` char(81),
OUT `suc6` TINYINT
)
labelBegin: begin
declare r0 int unsigned default 0;
declare c0 int unsigned default 0;
declare r2 int unsigned default 0;
declare c2 int unsigned default 0;
declare n int unsigned default 0;
declare num int unsigned default 1;
declare fnd boolean default 0;
declare r1 int unsigned default 1;
declare c1 int unsigned default 1;
declare r1c int unsigned default 0;
declare c1c int unsigned default 0;
declare gridTmp char(81);
set gridTmp = grid;
set suc6 = 0;
while r0 < 9 do
set c0 = 0;
while c0 < 9 do
if substr(gridTmp,(r0)*9+c0+1,1)='0' then
/* do 0; */
set n = 0;
labelN: while n < 9 do
set n = n+1;
set num = n;
set fnd = 0;
/*rows*/
set r2 = 0;
labelA: while r2 < 9 do
if r2<>r0 and substr(gridTmp,(r2)*9+c0+1,1)=num then
set fnd = 1;
leave labelA;
end if;
set r2 = r2+1;
end while;
if(fnd=1) then
iterate labelN;
end if;
/*columns*/
set c2 = 0;
labelA2: while c2 < 9 do
if c2<>c0 and substr(gridTmp,(r0)*9+c2+1,1)=num then
set fnd = 1;
leave labelA2;
end if;
set c2 = c2+1;
end while;
if(fnd=1) then
iterate labelN;
end if;
/*boxes*/
set r1 = FLOOR(r0/3)*3;
set c1 = FLOOR(c0/3)*3;
set r1c = r1;
labelB: while r1c < r1+3 do
set c1c = c1;
while c1c < c1+3 do
if (r0<>r1c or c0<>c1c) and substr(gridTmp,(r1c)*9+c1c+1,1)=num then
set fnd = 1;
leave labelB;
end if;
set c1c = c1c+1;
end while;
set r1c = r1c+1;
end while;
if(fnd=1) then
iterate labelN;
end if;
/*try it out*/
set gridTmp = CONCAT(substr(gridTmp,1,r0*9+c0),num,substr(gridTmp,r0*9+c0+2));
CALL sudoku(gridTmp,gridTmp,fnd);
if fnd=0 then
set gridTmp = grid;
set suc6 = 0;
else
set suc6 = fnd;
set result = gridTmp;
INSERT INTO sudokutable(sudoku) VALUES(gridTmp);
leave labelBegin;
end if;
end while;
leave labelBegin;
end if;
set c0 = c0+1;
end while;
set r0=r0+1;
end while;
if instr(gridTmp,'0')>0 then
set suc6 = 0;
set result = '';
else
set suc6 = 1;
set result = gridTmp;
INSERT INTO sudokutable(sudoku) VALUES(gridTmp);
end if;
end
SET @@SESSION.max_sp_recursion_depth = 255;
delete from sudokutable;
/*000000030050607004008903760000000520000216000042000000087302100200108090030000000
--796481235351627984428953761613894527579216348842735619987342156265178493134569872
--796481235351627984428953761613894520000216000042000000087302100200108090030000000 --SQL-server
*/
CALL `sudoku`('796481235351627984428953761613894527579216348842735619987342156260108090030000000', @sol, @suc6);
select @sol,@suc6,sudoku from sudokutable;
Tested in Heidi SQL. Somehow produces more records than SQL-server. To be investigated... ;)