Few days ago when I was searching about a solution of some issue I stumble upon a discussion thread. Someone asked about whether SQL queries can solve any real life problem. Of course, we know technologies of Oracle automate real life processes and make them easy. But this thread rang in my mind and caused me to search on if there any particular real life issue that can be solved with help of Oracle query.
And I found it. A guy has developed a query to solve Sudoku problems. When I looked at the query I was surprised and tested for 3 to 4 Sudoku ranging from easy to difficult ones.
How To Solve Sudoku Using Single SQL Query
Here is our Sudoku problem
To solve the puzzle you need to put all digits in a single string. For space you need to give space in string.
Here our string will be:
"4 29 58 339 64 1 9 5 1 4 6 2 3 4 8 7 5 51 279 47 36 8"
Pass this string in below MAGICAL query
WITH solve_sudoku(sud_str,ind) AS (SELECT sud, INSTR(sud, ' ') FROM (SELECT '4 29 58 339 64 1 9 5 1 4 6 2 3 4 8 7 5 51 279 47 36 8' sud FROM dual) UNION ALL SELECT SUBSTR(sud_str, 1, ind - 1) || z || SUBSTR(sud_str, ind + 1), INSTR(sud_str, ' ', ind + 1) FROM solve_sudoku, (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(sud_str, TRUNC((ind - 1) / 9) * 9 + lp, 1) OR z = SUBSTR(sud_str, MOD(ind - 1, 9) - 8 + lp * 9, 1) OR z = SUBSTR(sud_str, MOD(TRUNC((ind - 1) / 3), 3) * 3 + TRUNC((ind - 1) / 27) * 27 + lp + TRUNC((lp - 1) / 3) * 6, 1))) SELECT sud_str FROM solve_sudoku WHERE ind = 0;
The output of above is a solved Sudoku in single string
Arrange it in a 9 x 9 square and you will get
Amazing isn’t it?
Do you want to know this cool guy? Do you want to thank him for showing you this amazing thing?
Hope you like the article and find it useful. We highly appreciate comments and feedback.
Hi I am Paras.
Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!