Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Solving SUDOKU thru SQL Code Expand / Collapse
Author
Message
Posted Monday, November 2, 2009 12:51 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 29, 2010 2:48 AM
Points: 124, Visits: 275
Recently i cross through one article where it says the following SQL Code used in oracle to solve SUDOKU Puzzles.

for 9x9 sudoku puzzles, the filled in numbers were passed as a string with a space for every unfilled square"
in the following example, string passed is '53 7 6 195 98 6 8 6 34 8 3 17 2 6 6 28 419 5 8 79' ( One space for every unfilled square, if 3 continuous boxes are unfilled, then there should be 3 spaces)



The following is the the code in Orcale used for the purpose.



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
/


I request our SQL experts to convert this code into SQL Server compatible code to solve sudoku puzzles.


You can check the article about this @ http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring


Regards
Jus
Post #812541
Posted Monday, November 2, 2009 1:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
Actually, I think you should give it a try first. If you run into problems, post what you have tried and we'll see what we can do to help you.

This looks like a good training opportunity to learn how to port code. Always a good thing to know.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #812551
Posted Monday, November 2, 2009 2:07 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 29, 2010 2:48 AM
Points: 124, Visits: 275
Lynn..you are right..I should have tried first. But when i saw the code i found few oracle objects like dual , connect which am unaware of...hence posted..this was not for any project..just out of curiosity i posted this....if any one can post solution, i can try comparing oracle vs sql server codes and will learn from there........thanks
Post #812582
Posted Tuesday, August 16, 2011 4:43 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 17,947, Visits: 15,941
Late - but maybe still worth it. I'll be posting a blog on it shortly.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1160867
Posted Tuesday, August 16, 2011 4:53 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:26 PM
Points: 880, Visits: 608
Just learned this yesterday, so I'll post a quick aside: Dual is a dummy object with one row.

select 'foo' from dual

is equivalent to the tsql statement

select 'foo'

which is invalid in ansi sql




Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Post #1160874
Posted Tuesday, August 16, 2011 5:34 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 17,947, Visits: 15,941
Blog post sneak peek.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1160889
Posted Tuesday, August 16, 2011 6:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
SQLRNNR (8/16/2011)
Blog post sneak peek.


Sneak peek didn't work, at least for me.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1160896
Posted Tuesday, August 16, 2011 6:15 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 17,947, Visits: 15,941
Lynn Pettis (8/16/2011)
SQLRNNR (8/16/2011)
Blog post sneak peek.


Sneak peek didn't work, at least for me.


Crud - I had hoped it would since it worked for me. It is scheduled to post 8/17 at 6AM (Pacific)




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1160899
Posted Wednesday, August 17, 2011 7:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:30 AM
Points: 268, Visits: 960
I don't care about solving SUDOKU with T-SQL. -- I like to do that myself. Does anyone have code to create guaranteed solvable SUDOKU puzzles?
Post #1161156
Posted Wednesday, August 17, 2011 8:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 9:37 AM
Points: 1,343, Visits: 7,187
That is cool SQLRNNR.

I don't know if this would always work, but this at least works for the given puzzle to return it in 9 x 9:

SELECT TOP 9 SUBSTRING(s, ROW_NUMBER() OVER (ORDER BY s) * 9 - 8, ROW_NUMBER() OVER (ORDER BY s) * 9 - (ROW_NUMBER() OVER (ORDER BY s) * 9 - 8))
FROM x
WHERE ind = 0


bc
Post #1161159
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse