Home » SQL & PL/SQL » SQL & PL/SQL » SUDOKU (Oracle 10.2.0.1.0 windows 2003 server)
SUDOKU Fri, 26 March 2010 04:44
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

[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
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Kevin Meade Messages: 2102Registered: 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
 rleishman Messages: 3727Registered: 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
 Kevin Meade Messages: 2102Registered: 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.

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

sriram
Re: SUDOKU [message #449416 is a reply to message #449013] Mon, 29 March 2010 08:16
 Kevin Meade Messages: 2102Registered: 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
@ 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

[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
 rleishman Messages: 3727Registered: 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
 International_DBA Messages: 5Registered: 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: Mon Jul 24 12:12:02 CDT 2017

Total time taken to generate the page: 0.28194 seconds