TSQL Sudoku

Comments: 9 Comments
Published on: August 17, 2011

I am a big Sudoku fan.  Typically if I need a break, I will break out a Sudoku puzzle from any of a number of different sources (Websudoku, Android Apps, Puzzle Books).  Over time, I have come across a solution here or there to solve these puzzles via TSQL.

There are a few of these solutions out there already, such as one by Itzik Ben-Gan (which I can’t get to download without the file corrupting so I still haven’t seen it), or this one on SSC (which works most of the time but does provide inaccurate results from time to time).  I still wanted something to do this via CTE (much like the solution by Itzik is described to be at the link provided – if you have that code, I want to SEE it).

Just a couple of years ago, there was a post at SSC asking for some help converting a solution from Oracle to TSQL.  I checked out that code and worked on it for a day or two.  Then I got busy with other work that replaced the pet project.  I hadn’t given the idea much thought until just a few days ago as I was browsing my Topic list I had been building for articles.

This solution stuck with me this time around and I wanted to finish it up.  The Oracle solution for whatever reason made a lot more sense to me this time around, and I made great progress quickly.  It was actually this project that I was working on that prompted another post.  While working through the solution, I learned a fair amount about both flavors of SQL.  So, in preface to continuing to read here, you may want to check out the other article real quick since it pertains to some of the conversions done in this project.

Problems First

The OP supplied the Oracle solution asking for help in creating a TSQL Solution.  Here is that Oracle version.

[codesyntax lang=”sql”]

[/codesyntax]

If you read that other post I mentioned, you will quickly identify 5 functions/objects in use in this script that just don’t work in TSQL.  Those are:  dual, instr, substr, connect by, and trunc.  I did not mention mod in my other post, but mod is also done differently in TSQL than in Oracle.  I thought this one was a bit obvious and stuck with the top 5 ;).

Solution

After figuring out some of the subtle differences between commands and the best way to approach this, I was able to come up with a TSQL solution that works.  Take not first of that last where clause in the CTE of the Oracle solution.  That clause is very similar to what I refer to as the train-stop method to get unique paths in a hierarchy.  There are several methods to do similar functionality – I have concatenated strings with Stuff as wells cast to produce this functionality.

So here goes with the first rendition of this query.

[codesyntax lang=”tsql”]

[/codesyntax]

Notice that I have chosen to use an Itzik style numbers table/CTE.  This functions as my “dual” table translation and is necessary in the remainder of the query.  The final where clause of the CTE is simplified in TSQL by simply removing the TRUNC commands.  The original solution was merely removing the decimal precision.  In TSQL, the conversion is done to INT implicitly in this case.  I need to test a few more cases, but so far it works without error.

What this does not do…

This is the first rendition of the script.  Currently, it only returns the number sequence in one big long string.  I am working on modifying this script to produce a grid layout with the solution.  I envision this will require the use of PIVOT and possibly UNPIVOT to get me close.  In addition, I expect that further string manipulation will be needed – such as stuffing commas and then splitting it to make the PIVOT/UNPIVOT easier.  I’ll have to try some things and figure it out.  Also, I expect that some explicit conversions may be best in this query.  That could help improve performance a tad.

This, to this point, has been a fun diversion.  This has helped to learn a bit about Oracle, hierarchies, and to do a little math – all in one.  Better yet is that there is still work to be done on it and more learning.  If you have ideas how to make it better – I am very interested.

Top 5 Oracle Nuances I learned Today

Categories: News, Professional, SSC
Tags: ,
Comments: 1 Comment
Published on: August 16, 2011

I don’t do much with Oracle – at all.  Once in a blue moon, I find a little project to do that might involve Oracle.  I have never put a lot of thought to the differences between SQL and Oracle.  On the pet project I am doing right now, I put a little more thought into those differences and finally decided to write a little something about five things I am working with in the Oracle world and how those translate (or at least how I translated them) to the SQL world.

Let’s start with some very similar commands.

  1. substr().  In SQL, this translates to substring.  Easy enough right?  There is one more difference between the two than just the name.  The parameters are ordered differently in substr() than they are in substring().   Pay careful attention to your parameter sequence when converting this function from Oracle to SQL Server.
  2. instr().  This one is less obvious.  I have used PatIndex() and CharIndex() for this one – depends on needed functionality.  If you understand that instr is searching for a value within a string – it makes it a little easier to understand.  Also knowing that PatIndex searches for “Patterns” and Charindex() searches for a character is helpful.  If you need to supply the optional parameter used by instr(), then you should use Charindex.  Though not entirely the same – similar functionality is available in SQL for the instr() function.
  3. trunc().  This is a function used in Oracle to convert date and numbers to a shorter format (either different date format or fewer decimal places).  This is achieved through different means in SQL.  Two common methods are cast() and convert().
  4. dual.  This is not a function.  This is an internal table containing a single row.  There are many uses for this internal table.  One common use is equivalent to the Numbers/Tally table in SQL server.  Pick your favorite numbers/tally table method in these types of cases.
  5. connect by.  This is actually a pretty cool piece of functionality unique to Oracle.  I have seen this used in recursive CTEs to help control the hierarchy.  In these cases, it limits the result set to rows meeting the criteria of the connect by statement.  Similar functionality can be achieved through use of Joins and the Where clause.  This is a command that would be really cool in SQL.  It is true that you can build the hierarchy without this command in SQL.  I think it would help make that task easier and give it more flexibility.  It would also make it a little easier to read/understand.

This is all pretty cool.  It should be pretty straight forward stuff for most DBAs.  Some day, maybe we’ll explore a post dedicated to connect by and how some of the features of that command can be translated into SQL.  For now, just know that there is some commonality between the two RDBMSs – just a little translation may be necessary.

page 1 of 1








Calendar
November 2017
M T W T F S S
« Oct    
 12345
6789101112
13141516171819
20212223242526
27282930  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, November 19, 2017