Solving SUDOKU thru SQL Code

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Solving SUDOKU thru SQL Code

28 posts, Page 1 of 3

1 2 3 »»»

Solving SUDOKU thru SQL Code

Rate Topic

Display Mode

Topic Options

Author

Message

Jus

Posted Monday, November 02, 2009 12:51 PM

SSC-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

Lynn Pettis

Posted Monday, November 02, 2009 1:23 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 21,617, Visits: 27,451

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.

Cool

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

Jus

Posted Monday, November 02, 2009 2:07 PM

SSC-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

SQLRNNR

Posted Tuesday, August 16, 2011 4:43 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1160867

weitzera

Posted Tuesday, August 16, 2011 4:53 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:59 PM
Points: 830, Visits: 526

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

SQLRNNR

Posted Tuesday, August 16, 2011 5:34 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

Blog post sneak peek.

Post #1160889

Lynn Pettis

Posted Tuesday, August 16, 2011 6:05 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 21,617, Visits: 27,451

SQLRNNR (8/16/2011)

Blog post sneak peek.

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

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1160896

SQLRNNR

Posted Tuesday, August 16, 2011 6:15 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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)

Post #1160899

nwerner

Posted Wednesday, August 17, 2011 7:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:08 PM
Points: 188, Visits: 585

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

bc_

Posted Wednesday, August 17, 2011 8:01 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 1,304, Visits: 7,119

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 »

28 posts, Page 1 of 3

1 2 3 »»»

Permissions