Wednesday, December 28, 2011

Wordfeud and regular expression in SQL

Three family members invited me to play Wordfeud with them this Christmas. At the end of such a game losing badly to my sister-in-law and with only a few letters left I was thinking about how I could find candidate words using regular expression in SQL.

First I found a list of Norwegian words on the net. I should have taken the effort to get hold of the same word list as Wordfeud is using. The list was loaded into a table using SQL Developer.

Say you want to see if there is any word that start with N and uses only the letters C, F, S, and A. The following SQL may find it:


select word
from norwegians
where regexp_substr(word,'^n[cfsa]+$') is not null ;


For those not familiar with regular expressions, well, just Google or check out any good book on Perl. In short, the ^ and $ means the beginning and end of line (word in this case) respectively, [] is used to create a group of letters; [cfsa] means any letter of c, f, s, and a; + means one or more occurrences.

Now I only need to make a mobile interface to this, and create a function that calculate the score for each word based on Wordfeud's rules. Next Christmas, maybe. I do know there are apps ready to suggest this, but that is not half as fun :-)