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.
SQL Scrabble puzzle
- @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.
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) BULK INSERT TLW06 FROM 'S:\Scrabble\TWL06.txt' WITH (DATAFILETYPE='char');
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) BULK INSERT SOWPODS FROM 'S:\Scrabble\SOWPODS.txt' WITH (DATAFILETYPE='char',FIRSTROW=3);
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.
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,'?',''))