I am a big Sudoku fan. Typically if I need a break, I will break out a Sudoku puzzle from any of a number of different sources (Websudoku, Android Apps, Puzzle Books). Over time, I have come across a solution here or there to solve these puzzles via TSQL.
There are a few of these solutions out there already, such as one by Itzik Ben-Gan (which I can’t get to download without the file corrupting so I still haven’t seen it), or this one on SSC (which works most of the time but does provide inaccurate results from time to time). I still wanted something to do this via CTE (much like the solution by Itzik is described to be at the link provided – if you have that code, I want to SEE it).
Just a couple of years ago, there was a post at SSC asking for some help converting a solution from Oracle to TSQL. I checked out that code and worked on it for a day or two. Then I got busy with other work that replaced the pet project. I hadn’t given the idea much thought until just a few days ago as I was browsing my Topic list I had been building for articles.
This solution stuck with me this time around and I wanted to finish it up. The Oracle solution for whatever reason made a lot more sense to me this time around, and I made great progress quickly. It was actually this project that I was working on that prompted another post. While working through the solution, I learned a fair amount about both flavors of SQL. So, in preface to continuing to read here, you may want to check out the other article real quick since it pertains to some of the conversions done in this project.
Problems First
The OP supplied the Oracle solution asking for help in creating a TSQL Solution. Here is that Oracle version.
[codesyntax lang=”sql”]
with x( s, ind ) as
( select sud, instr( sud, ' ' )
from ( select '53 7 6 195 98 6 8 6 34 8 3 17 2 6 6 28 419 5 8 79' sud from dual )
union all
select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )
, instr( s, ' ', ind + 1 )
from x
, ( select to_char( rownum ) z
from dual
connect by rownum <= 9
) z
where ind > 0
and not exists ( select null
from ( select rownum lp
from dual
connect by rownum <= 9
)
where z = substr( s, trunc( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
or z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
or z = substr( s, mod( trunc( ( ind - 1 ) / 3 ), 3 ) * 3
+ trunc( ( ind - 1 ) / 27 ) * 27 + lp
+ trunc( ( lp - 1 ) / 3 ) * 6
, 1 )
)
)
select s
from x
where ind = 0
/[/codesyntax]
If you read that other post I mentioned, you will quickly identify 5 functions/objects in use in this script that just don’t work in TSQL. Those are: dual, instr, substr, connect by, and trunc. I did not mention mod in my other post, but mod is also done differently in TSQL than in Oracle. I thought this one was a bit obvious and stuck with the top 5 ;).
Solution
After figuring out some of the subtle differences between commands and the best way to approach this, I was able to come up with a TSQL solution that works. Take not first of that last where clause in the CTE of the Oracle solution. That clause is very similar to what I refer to as the train-stop method to get unique paths in a hierarchy. There are several methods to do similar functionality – I have concatenated strings with Stuff as wells cast to produce this functionality.
So here goes with the first rendition of this query.
[codesyntax lang=”tsql”]
DECLARE @SudokuGivens VARCHAR(100)
SET @SudokuGivens = '53 7 6 195 98 6 8 6 34 8 3 17 2 6 6 28 419 5 8 79'
;
WITH
E1(N) AS ( --=== Create Ten 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 --10
)
,dual(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E1)
,x( s, ind ) as
( select CONVERT(VARCHAR(100),sud), CHARINDEX(' ',Ss.sud ) AS ind
from
( select @SudokuGivens AS sud from dual ) Ss
union all
select CONVERT(VARCHAR(100),substring( s, 1, ind - 1 ) + z + substring( s, ind + 1 ,LEN(s)))
, CHARINDEX( ' ', s, ind + 1 ) AS ind
from x
CROSS APPLY ( select convert(VARCHAR(25), N ) z
from dual
Where N <= 9
) z
where ind > 0
and not exists ( select null
from ( select N AS lp
from dual
Where N <= 9
) ww
where z = substring( s, ( ind - 1)% 9 - 8 + lp * 9, 1 )
or z = substring( s, ( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
or z = substring( s, (( ( ind - 1 ) / 3 )%3) * 3
+ ( ( ind - 1 ) / 27 ) * 27 + lp
+ ( ( lp - 1 ) / 3 ) * 6
, 1 )
)
)
select DISTINCT s
from x
where ind = 0[/codesyntax]
Notice that I have chosen to use an Itzik style numbers table/CTE. This functions as my “dual” table translation and is necessary in the remainder of the query. The final where clause of the CTE is simplified in TSQL by simply removing the TRUNC commands. The original solution was merely removing the decimal precision. In TSQL, the conversion is done to INT implicitly in this case. I need to test a few more cases, but so far it works without error.
What this does not do…
This is the first rendition of the script. Currently, it only returns the number sequence in one big long string. I am working on modifying this script to produce a grid layout with the solution. I envision this will require the use of PIVOT and possibly UNPIVOT to get me close. In addition, I expect that further string manipulation will be needed – such as stuffing commas and then splitting it to make the PIVOT/UNPIVOT easier. I’ll have to try some things and figure it out. Also, I expect that some explicit conversions may be best in this query. That could help improve performance a tad.
This, to this point, has been a fun diversion. This has helped to learn a bit about Oracle, hierarchies, and to do a little math – all in one. Better yet is that there is still work to be done on it and more learning. If you have ideas how to make it better – I am very interested.
