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  |
 |
ramoradba
Messages: 2457 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
[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: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #449228 is a reply to message #449187] |
Sun, 28 March 2010 07:01   |
rleishman
Messages: 3728 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 #449523 is a reply to message #449416] |
Tue, 30 March 2010 04:57   |
 |
ramoradba
Messages: 2457 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 
[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: 3728 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  |
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
|
|
|
Goto Forum:
Current Time: Sat Feb 15 08:21:18 CST 2025
|