Home » SQL & PL/SQL » SQL & PL/SQL » SUDOKU (Oracle 10.2.0.1.0 windows 2003 server)
SUDOKU [message #449013] Fri, 26 March 2010 04:44 Go to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Any Other Alternate solution to this?


SQL> SELECT
  2  CASE WHEN ROWNUM IN (3, 6)
  3       THEN substr(fin_str,1,3)||' | '||substr(fin_str,4,3)
  4            ||' | '||substr(fin_str,7,3) ||chr(10)||'----------------'
  5       ELSE substr(fin_str,1,3)||' | '||substr(fin_str,4,3)
  6            ||' | '||substr(fin_str,7,3)
  7  END sudoku
  8  FROM
  9  (SELECT substr(fin_str,(LEVEL - 1) * 9 + 1, 9) as fin_str
 10    FROM
 11   (SELECT fin_str
 12    FROM
 13   (SELECT fin_str FROM dual
 14    MODEL
 15    DIMENSION BY (0 i )
 16    MEASURES (CAST(NULL AS NUMBER) num,  CAST(NULL AS NUMBER) tmp,
 17              CAST(NULL AS NUMBER) fin , CAST(NULL AS NUMBER) tmp_fin,
 18       CAST(NULL AS varchar2(4000)) str,
 19              CAST(NULL AS varchar2(4000)) fin_str)
 20    RULES ITERATE (81)
 21    (num[ITERATION_NUMBER] = 1,
 22     tmp_fin[ANY] = mod(nvl(fin[cv()-1], 0), 9) + 1,
 23     str[ANY] = str[cv()-1] || to_char(tmp_fin[cv()]),
 24     fin[ANY] = nvl(fin [cv()-1], 0) + num[cv()] + nvl(tmp[cv()], 0),
 25     tmp[ANY] = CASE WHEN cv(i)+1 IN (9,18,36,45,63,72)
 26       THEN  3
 27       WHEN cv(i)+1 IN (27,54,81)
 28              THEN  4 END,
 29     fin_str[ANY] = CASE WHEN str[cv() + 1] IS NULL
 30                         THEN str[cv()] END
 31    )
 32   )
 33  WHERE fin_str IS NOT NULL
 34  )
 35  CONNECT BY LEVEL <10
 36  );

SUDOKU
--------------------------------
123 | 456 | 789
456 | 789 | 123
789 | 123 | 456
----------------

234 | 567 | 891
567 | 891 | 234
891 | 234 | 567
----------------

345 | 678 | 912
678 | 912 | 345
912 | 345 | 678

9 rows selected.

SQL>


source www.oraqa.com
sriram Smile

[Updated on: Fri, 26 March 2010 04:49]

Report message to a moderator

Re: SUDOKU [message #449020 is a reply to message #449013] Fri, 26 March 2010 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is easy to generate such charts and it was done far before sudoku was popular, this was called magical squares.
It is far more difficult to solve a sudoku in SQL.
Good luck!

Regards
Michel

[Edit: typo]

[Updated on: Fri, 19 August 2011 08:19]

Report message to a moderator

Re: SUDOKU [message #449187 is a reply to message #449013] Sat, 27 March 2010 11:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Wasn't Frank or Ross one of our experts at solving these kinds of problems?

Kevin
Re: SUDOKU [message #449228 is a reply to message #449187] Sun, 28 March 2010 07:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT substr(str, 1 + mod(rownum-1,3)*3 + trunc((rownum-1)/3), 9)
FROM (
    SELECT '123456789123456789' as str
    FROM dual
)
CONNECT BY LEVEL <= 9


OK, I'll bite: what's the point?

Ross Leishman
Re: SUDOKU [message #449241 is a reply to message #449013] Sun, 28 March 2010 12:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
I have no clue why the OP wanted a solution.

But I do remember that you Ross are an expert at solving useless problems. Smile

Kevin

[Updated on: Sun, 28 March 2010 12:43]

Report message to a moderator

Re: SUDOKU [message #449242 is a reply to message #449241] Sun, 28 March 2010 14:44 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thanks To All for Your comments Like "usless problem"


sriram
Re: SUDOKU [message #449416 is a reply to message #449013] Mon, 29 March 2010 08:16 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
All in jest of course. I am sure your need is important. I hope that Ross's solution works for you.

Good luck, Kevin
Re: SUDOKU [message #449523 is a reply to message #449416] Tue, 30 March 2010 04:57 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thanks All fOr your replies.
@ Ross thank you very much for reply...
@ kevin
My requirement is to get the SUDOKU Output based on given unstructured string

ind> SELECT substr(str, 1 + mod(rownum-1,3)*3 + trunc((rownum-1)/3), 9)
  2  FROM (
  3      SELECT '123456789123456789' as str
  4      FROM dual
  5  )
  6  CONNECT BY LEVEL <= 9
  7  /

SUBSTR(ST
---------
123456789
456789123
789123456
234567891
567891234
891234567
345678912
678912345
912345678

9 rows selected.

ind> set feed off
ind> @ c:\sudomod.sql

SUDOKU
---------
   56  2
 63
   2   37
956   173
327  14
481  9
6   7
    2 381
83

SUDOKU
---------
174563829
263897514
598214637
956482173
327651498
481739256
612378945
745926381
839145762
ind> 
ind> 


That is for the string....
" ' 56 2 63 2 37 5 173327 14 1 9 6 7 2 38183 '"

I added spaces in the string But it`s not showing properly the spaces..
So ..Pease replace the '*' with one single space.

'***56**2**63*********2***37*5****173327**14****1**9***6***7********2*38183*******'





Regards
sriram Smile

[Updated on: Tue, 30 March 2010 05:05]

Report message to a moderator

Re: SUDOKU [message #449548 is a reply to message #449523] Tue, 30 March 2010 06:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do you see how this this is different from your first post? First you asked how you could use SQL to output a grid of 81 numbers in a specified format. Now you are asking for a Sudoku solver.

Why would you want to write a Sudoku solver in SQL? It seems like an unnecessarily difficult complexity to impose - surely there are more appropriate languages.

There are dozens of sudoku solvers on the web, many of which describe their techniques in detail, if not their programatic algorithms.

Based on a limited understanding of Sudoku, you could probably solve it by brute force with pure SQL (see this thread for an example of brute force solving in SQL: http://www.orafaq.com/forum/mv/msg/127104/356826/78693#msg_num_6), but that is technically against the rules for Sudoku solvers.

The true Sudoku solver must use deductive logic to derive the value of each cell. You can get by with just few different rules of logic with simple puzzles, but more complex puzzles may require many more rules.

The complexity of the logic involved would - I believe - require procedural logic unavailable with pure SQL. So you would be forced to use procedural logic offered by either PL/SQL or the MODEL clause.

The solution then is to simply translate the accepted solving algorithms into MODEL clauses or PL/SQL.

Hard, yes, but not really an interesting problem. Coming up with the algorithms is the interesting part - and you can do that in any procedural language. If you use somebody else's algorithms, then it just becomes a dull translation exercise.

Creating a deductive Sudoku solver (rather than brute force) in pure SQL would be an interesting problem, but it's not one I believe can be solved.

Ross Leishman
Re: SUDOKU [message #509555 is a reply to message #449013] Sun, 29 May 2011 16:45 Go to previous message
International_DBA
Messages: 5
Registered: January 2010
Location: England
Junior Member
I don't know if you are still interested but I have posted a PL/SQL Sudoku solver on my blog. It is in 4 parts so it is too big to reproduce here. Apparently I cannot give you the URL as I have not posted enough messages yet.

[Updated on: Mon, 21 September 2015 07:22] by Moderator

Report message to a moderator

Previous Topic: Custom integrity constraint
Next Topic: multiple foreign key
Goto Forum:
  


Current Time: Sun Sep 25 04:24:57 CDT 2016

Total time taken to generate the page: 0.14500 seconds