Consequential Solutions Ltd


SQL Puzzle results

Latest solutions

It’s nearly Christmas, when the family games come out and everyone gets a bit more competitive, which is hard to imagine when it comes to DBAs and developers! So here is a little challenge to distract you from figuring out what presents to buy. It would be interesting to see who can claim the top solutions, DBA or developer so please let us know which you are along with your solution, or if you are something completely different and just like to solve puzzles.

First prepare
Go read the very well written article “The SQL of Scrabble and Rapping” by Phil Factor and fully understand the “ValidWordsInLetters” function.
Find and download the TWL06.txt words file which can be loaded into your words table for checking the valid words for this puzzle.
sql scrabble puzzle board

Can you beat our fastest solution?

SQL Scrabble puzzle

Your challenge is to work your SQL magic to update what’s there with your own improvements.
Reduce the resource usage so in turn it works more efficiently in the expected environment and enable it to cope with the following inputs.
  • @Letters is what you have in your rack
  • “?” represents any letter (a blank)
  • @Contains is the letters that must be present in the returned words as if you are adding to a word already on the board.
declare @Letters nvarchar(max) = ‘OSAEIRUHEFQ??’
declare @Contains nvarchar(max) = ‘R’

Hint 1 

Creating the table that holds all the valid words from a text file downloaded from the internet may seem like a simple thing to do for some but for many it will be unchartered territory so here is a quick rundown of how to do it.

There may be a more efficient way but that’s for you to find out, we know of at least one improvement to what we are describing below but to keep it simple that won’t be revealed until the final solution.

First download the text files which can be found using the following links, TWL06 and SOWPODS are the main English (US and UK) dictionaries depending on which country you are in.

Now you need to decide which database you want to use and we chose a newly created one called “Words” so we made sure we were connected to that database and not the Master database. We then created a new table for each of the word lists and bulk inserted from the text files using the following method. The dictionaries have a maximum word length of fifteen characters.

create table TLW06 (Word varchar(15) primary key clustered)

Note that the SOWPODS file has some header text so we have to specify FIRSTROW=3 to skip it.

create table SOWPODS (Word varchar(15) primary key clustered)

The final thing you need to be aware of is what collation you are using as the TWL06 list is all uppercase whereas the SOWPODS list is all lowercase so if you are using a case sensitive collation you will have to cater for that difference in your solution.

We are going to assume the collation used is case insensitive so we can ignore that difference.

Congratulations, assuming you have the correct permissions to do all of the above and no errors occurred you should now have your list of words to play with.

Hint 2

One of the things you have to do for this puzzle is to figure out how to deal with the wildcard characters represented by the question mark (“?”). They have to be treated differently as they match any character so the first step is to find out how many wildcards you have. There are many ways to count them but the fastest and easiest way is to simply replace the character with a blank and then compare the length to the original length as shown below.

declare @Wildcards int = LEN(@Letters)-LEN(REPLACE(@Letters,'?',''))
Now you know how many wildcard characters you have to play around with!
The Solutions… 
Send us an email with your puzzle answer/s and comment below on how you found the challenge. Tell a colleague and see if they can beat your puzzle time, or ours, once it’s posted. The top performing solutions will feature here shortly after they’re verified.
A much faster puzzle solution with a detailed explanation, written by our elite team, will be added here in January. (… Xmas countdown here)
This should be enough time for you to at least give it your best shot. ☺
Happy solving!
The Consequential Solutions Team


    1. Vern Jewett

      I did an early solution that used a row-based index instead of ColumnStore. It was a little slower than my final, but still not bad for elapsed time. It could be tested quickly by just replacing the ColumnStore index with standard, then it could be deployed on any version.
      Thanks again for the fun challenge.

Leave a Reply

Your email address will not be published. Required fields are marked *

We use Cookies - By using this site or closing this you agree to our Cookies policy.
Accept Cookies