{"id":758,"date":"2009-12-25T00:00:00","date_gmt":"2009-12-23T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-sql-of-scrabble-and-rapping\/"},"modified":"2021-09-29T16:22:03","modified_gmt":"2021-09-29T16:22:03","slug":"the-sql-of-scrabble-and-rapping","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-sql-of-scrabble-and-rapping\/","title":{"rendered":"The SQL of Scrabble and Rapping"},"content":{"rendered":"<h2>Preamble<\/h2>\n<p class=\"MsoNormal\">Sometimes, when you tackle a different problem in SQL, one can hit on techniques that come in handy in all sorts of other contexts.\u00a0 This is the theme of this article, which uses a bank of all common words of the English language.\u00a0<\/p>\n<h2>Cheating at Scrabble<\/h2>\n<p class=\"MsoNormal\">\u00a0I was playing Scrabble the other day and faced, as usual with an impossible hand. Scrabble is, as you probably know, a game where you pick tiles from a sack, and each tile has a letter written on it. You are allowed up to seven tiles at a time, and you have to place these, made up into words, on a game board marked with a 15-by-15 grid. The words are formed across and down in crossword fashion, and must be real words in common use. Each letter is scored individually, but the score is boosted by special squares on the board that give you double, or triple, word, or letter, scores.<\/p>\n<p class=\"MsoNormal\">It occurred to me, as it must have done to many others, that one could cheat at this game with a surreptitious iPhone or iPod and a simple word bank.<\/p>\n<p class=\"MsoNormal\">If you based this application on SQL Server, using a simple HTML interface, it would be easy to find all the words that could be made up from your seven tiles. Because you will need to link in with another word, that will come to eight letters. In some denser games, even more than eight-letter words are made as more existing words are crossed.<\/p>\n<p class=\"MsoNormal\">The first exercise will be to find all the permutation of the characters in your hand. Actually, if you are being subtle, you can restrict yourself to a subset, since only a small number of the combination of vowels and consonants are actually allowed in English. We won&#8217;t be subtle here: we&#8217;ll use the brute-force attack to the problem. The simplest way is to do a series of joins to a table of letters, but I&#8217;ll try a more flexible approach that uses a variation of the card-sharper&#8217;s shuffle instead. To do this, you will firstly need a number table. This number table is used frequently in SQL and you may already have one in your test database.\u00a0 If you haven&#8217;t, this stored procedure will deal with the task of creating and populating the number table.<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF EXISTS ( SELECT\u00a0 'found'  \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.objects\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 name='MaybeBuildNumberTable' ) \r\n\u00a0\u00a0\u00a0 DROP PROCEDURE MaybeBuildNumberTable\r\ngo\r\nCREATE PROCEDURE [dbo].[MaybeBuildNumberTable] \r\nAS \r\nBEGIN \r\nSET NOCOUNT ON \r\nIF NOT EXISTS ( SELECT\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 dbo.sysobjects\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 id=OBJECT_ID(N'[dbo].[Numbers]')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND OBJECTPROPERTY(id, N'IsUserTable')=1 ) \r\n\u00a0 BEGIN \r\n\u00a0\u00a0\u00a0 CREATE TABLE [dbo].[Numbers]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [number] [int],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT [Index_Numbers] PRIMARY KEY CLUSTERED ([number] ASC)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON [PRIMARY]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0 ON\u00a0 [PRIMARY] \r\n\u00a0 END\r\nIF NOT EXISTS(SELECT 1 FROM numbers WHERE number=99999)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 TRUNCATE TABLE numbers\r\n;WITH Digits(i)\r\nAS (SELECT i\r\n\u00a0\u00a0 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS X(i))\r\nINSERT INTO numbers(number)\r\nSELECT (D5.i*100000 + D4.i*10000 + D3.i * 1000 + D2.i * 100 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + D1.i * 10 + D0.i + 1) AS seq\r\n\u00a0\u00a0\u00a0 FROM Digits AS D0, Digits AS D1, Digits AS D2, Digits AS D3, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Digits AS D4, Digits AS D5\r\n\u00a0\u00a0\u00a0 END\r\n\u00a0\u00a0\u00a0 \r\nEND\r\ngo\r\nExecute MaybeBuildNumberTable\r\n<\/pre>\n<p class=\"MsoNormal\">With these basics out of the way, we can now create the Table-Valued Function that returns the permutations in sequence of the characters you give it. This will work with any ASCII character and can be altered to deal with unicode, of course. All we are doing here is creating an empty table of the right size and then filling it with all the permutations of the characters you supply to the function. Normally, permutations will be done with a series of self-joins to a table with all the characters, one per row, but here we want something that will be useful when you do not know the length of the string in advance. (Permutations are great for doing many of the &#8216;graph&#8217; problems, such as finding the shortest route, network routing, or\u00a0 time-tabling)<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\r\nif exists (select 'found' from sys.objects where name ='PermutationsOf')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 drop function PermutationsOf\r\ngo\r\nCREATE FUNCTION PermutationsOf (@String VARCHAR(10))\r\n\/**\r\nsummary:\u00a0 &gt;\r\n\u00a0 returns a simple table of all the permutations of a string of up to\r\n\u00a0 eight characters. (one can do more if you are prepared to wait a bit)\r\n\u00a0 The algorithm is based on the idea of a cunjurors shuffle which \r\n\u00a0 guarantees to return the pack to its original order. In this case, \r\n\u00a0 it shuffles the string to every combination.\r\n\u00a0 It uses the 'quirky update' in order to do the shuffle.\r\nexample:\r\n\u00a0\u00a0\u00a0\u00a0 - code:\u00a0\u00a0\u00a0 --test for correctness\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Select count(*),string \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 from dbo.PermutationsOf ('12345678') \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 group by string having count(*)&gt;1\r\n\u00a0\u00a0\u00a0\u00a0 - code:\u00a0\u00a0\u00a0 Select * from dbo.PermutationsOf ('physical')\r\n\u00a0\u00a0\u00a0\u00a0 - code:\u00a0\u00a0\u00a0 Select * from dbo.PermutationsOf (null)\r\nreturns:\u00a0\u00a0\u00a0\u00a0\u00a0 a table listing all the alternative permutations of the\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 letters. If you gibe a string like '111' then all \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 permutations will be the same\r\n**\/\r\nRETURNS\u00a0 @alternatives TABLE \r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 number INT primary key,\r\n\u00a0\u00a0\u00a0\u00a0 String VARCHAR(10)\r\n\u00a0\u00a0\u00a0 )\r\nAS\r\nBEGIN\r\nDECLARE @LenString INT\r\nDECLARE @Iterations INT\r\nSELECT\u00a0 @LenString=LEN(@String),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @iterations=CASE @Lenstring\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 1 THEN 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 2 THEN 2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 3 THEN 6\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 4 THEN 24\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 5 THEN 120\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 6 THEN 720\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 7 THEN 5040\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN 8 THEN 40320\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\u00a0\r\nIF @iterations IS NULL \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT INTO @alternatives ( number,string)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT 1,'Sorry' UNION\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT 2,'String' UNION\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT 3,'wrong' UNION\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT 4,'length'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN\r\n\u00a0\u00a0 END\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nINSERT INTO @alternatives (Number, string)\r\n\u00a0\u00a0\u00a0 SELECT\u00a0 number,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ''\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 numbers\r\n\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 number&lt;=@iterations\r\nUPDATE\u00a0 @alternatives --progressively shuffle the string\r\nSET @string=string\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 =CASE WHEN number=0 THEN @string\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN number%5040=0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN SUBSTRING(@string, 8, 1)+SUBSTRING(@string, 7, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 6, 1)+SUBSTRING(@string, 5, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 4, 1)+SUBSTRING(@string, 2, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +LEFT(@string, 1)+SUBSTRING(@string, 3, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 9, @lenstring-4)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN number%720=0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN SUBSTRING(@string, 7, 1)+SUBSTRING(@string, 6, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 5, 1)+SUBSTRING(@string, 4, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 2, 1)+LEFT(@string, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 3, 1)+SUBSTRING(@string, 8, @lenstring-4)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN number%120=0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN SUBSTRING(@string, 6, 1)+SUBSTRING(@string, 5, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 4, 1)+SUBSTRING(@string, 2, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +LEFT(@string, 1)+SUBSTRING(@string, 3, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 7, @lenstring-4)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN number%24=0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN SUBSTRING(@string, 5, 1)+SUBSTRING(@string, 4, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 2, 1)+LEFT(@string, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 3, 1)+SUBSTRING(@string, 6, @lenstring-4)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN number%6=0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN SUBSTRING(@string, 4, 1)+SUBSTRING(@string, 2, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +LEFT(@string, 1)+SUBSTRING(@string, 3, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(@string, 5, @lenstring-4)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN number%3 IN (1, 2)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN SUBSTRING(@string, 2, 2)+LEFT(@String, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +RIGHT(@String, @lenstring-3)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE SUBSTRING(@string, 2, 1)+LEFT(@string, 1)\r\n\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+RIGHT(@String, @lenstring-2)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\u00a0 RETURN\r\n\u00a0\r\nEND\r\nGO\u00a0\r\n<\/pre>\n<p class=\"MsoNormal\">Now we have this working, we&#8217;ll need all the common words of English. We first create the table of words and stock it from our word bank that I provide in the downloads at the bottom of the article.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0IF EXISTS ( SELECT\u00a0 'found'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 information_Schema.tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 Table_Name='AllWords' ) \r\n\u00a0\u00a0\u00a0 DROP TABLE AllWords\r\ngo\r\n\u00a0\r\nCREATE TABLE AllWords\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 String VARCHAR(25) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0 CONSTRAINT [PK_CommonWords] PRIMARY KEY CLUSTERED ([String] ASC)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON [PRIMARY]\r\n\u00a0\u00a0\u00a0 )\r\nON\u00a0 [PRIMARY]\r\n\u00a0\r\ngo\r\n\/*\r\nnow we'll insert all the words into our dictionary\u00a0 table *\/\r\nDECLARE @commonwords TABLE (Word VARCHAR(255))\r\nINSERT\u00a0 INTO @CommonWords\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXECUTE master..xp_cmdshell 'type wordlist.txt'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nINSERT\u00a0 INTO AllWords (String)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 left(word,25)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 @commonwords\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 word IS NOT NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\ngo\r\n<\/pre>\n<p class=\"MsoNormal\">You would have to alter the path to the word list before using this, of course.\u00a0 I usually find that my seven tiles consist mostly of vowels so I couldn&#8217;t resist extracting lists of handy words with at least four consecutive vowels, just to make sure that everything was loaded properly<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Declare @list Varchar(Max)\r\nSELECT @List=coalesce(@list+', ','')+string FROM allwords\r\nWHERE PATINDEX('%[aeiou][aeiou][aeiou][aeiou]%',string) &gt;0\r\nSelect @List\r\n\/*aqueous, gooier, gooiest, obsequious, obsequiously, obsequiousness, onomatopoeia, pharmacopoeia, pharmacopoeias, plateaued, plateauing, queue, queued, queueing, queues, queuing, sequoia, sequoias\r\n*\/\r\n<\/pre>\n<p class=\"MsoNormal\">Though my favourite has five consecutive vowels<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT string FROM allwords\r\nWHERE PATINDEX('%[aeiou][aeiou][aeiou][aeiou][aeiou]%',string) &gt;0\r\n--queueing\r\n\u00a0\r\n<\/pre>\n<p class=\"MsoNormal\">Good grief, if you are short of vowels, there is plenty you can do, as there are words with seven or more consonants in a row (if you include Y as a consonant) &#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Declare @list Varchar(Max)\r\nSELECT @List=coalesce(@list+', ','')+string FROM allwords\r\nWHERE PATINDEX('%[^aeiou][^aeiou][^aeiou][^aeiou][^aeiou][^aeiou][^aeiou]%',string) &gt;0\r\nSelect @List\u00a0\u00a0\u00a0 \r\n\/* biorhythms, encrypts, rhythms, strychnine *\/\r\nBut most of the time, it is a shortage of vowels\r\n\/* you can quickly find the words with the most vowels (10 seems to be the highest) but these are in words like internationalization(10) and interdenominational (9)\r\n*\/\r\nSELECT\u00a0\u00a0 string,LEN(string)-LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String,'a',''), 'e',''), 'i',''), 'o',''), 'u',''))\r\nFROM\r\n\u00a0allwords\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nORDER BY LEN(string)-LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String,'a',''), 'e',''), 'i',''), 'o',''), 'u',''))DESC\u00a0 \r\n\u00a0\r\n\/* but it is more useful to have the ones with the highest proportion of vowels to consonants! *\/\r\n\u00a0DECLARE @list VARCHAR(MAX)\r\nSELECT @List=COALESCE(@list+', ','')+string FROM\r\n(\r\nSELECT TOP 60 string FROM\r\n\u00a0allwords\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nORDER BY ((LEN(string)-LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String,'a',''), 'e',''), 'i',''), 'o',''), 'u','')))*100.00) \/LEN(string) DESC\r\n)f\r\nSELECT @List \r\n\/*\r\noi, adieu, aerie, audio, eerie, queue, aeon, aide, aloe, aqua, area, aria, aura, auto, beau, ciao, ease, epee, euro, idea, iota, luau, oboe, ooze, ouzo, amoebae, anaemia, aquaria, aqueous, aureole, evacuee, sequoia, aah, acacia, ace, adagio, adieus, adieux, aerate, aerial, aeries, age, ago, aha, aid, ail, aim, air, airier, ale, amebae, amoeba, anemia, ape, apiece, apogee, are, arouse, ate, audacious*\/\r\n\u00a0\r\n\u00a0\r\n<\/pre>\n<p class=\"MsoNormal\">Now we have a function that will provide a table with all the permutation of between two and eight letters, we can use it to find those common words that can be built with your list of letters. Here we have an effective way of cheating at Scrabble. You&#8217;d have to use something like iWebKit to knock together a little application.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">if exists (select 'found' from sys.objects where name ='ValidWordsInLetters')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 drop function ValidWordsInLetters\r\ngo\r\nCREATE FUNCTION ValidWordsInLetters (@String VARCHAR(10))\r\n\/**\r\nsummary:\u00a0 &gt;\r\nexample:\r\n\u00a0\u00a0\u00a0\u00a0 - code:\u00a0 Select String from dbo.ValidWordsInLetters('CoFartua')\u00a0 \r\nreturns:\u00a0\u00a0\u00a0\u00a0\u00a0 a table listing up to the first hundred words\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 that can be made with the letters you supply. Good for \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Scrabble where you have SQL Server handy.\r\n**\/\r\nRETURNS\u00a0 @words TABLE \r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0 number INT identity(1,1) primary key,\r\n\u00a0\u00a0\u00a0\u00a0 String VARCHAR(10)\r\n\u00a0\u00a0\u00a0 )\r\nAS\r\nBEGIN\r\nDECLARE @rowcount INT, @ii INT, @LenString int\r\nDeclare @Strings Table (\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 number INT identity(1,1) primary key,\r\n\u00a0\u00a0\u00a0 String VARCHAR(10)\r\n\u00a0\u00a0\u00a0 )\r\ninsert into @strings(string)\r\nSelect distinct string \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 from dbo.PermutationsOf (@String) \r\n\u00a0\r\nSELECT\u00a0 @Rowcount=0, @LenString=LEN(@String),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ii=@LenString\r\nWHILE @ii&gt;2\r\n\u00a0\u00a0\u00a0 AND @rowcount&lt;100\r\n\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT\u00a0 INTO @words\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 distinct TOP 100 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 allwords.string\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 Allwords\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN @Strings a \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON LEFT(a.string, @ii)=allwords.string\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @Rowcount=@rowcount+@@Rowcount,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ii=@ii-1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 END\r\nreturn\r\nend\r\n\u00a0\r\n\/*\r\nSelect String from dbo.ValidWordsInLetters('CoFartua')\u00a0 \r\n*\/\r\n<\/pre>\n<h2>Rapping and doggerel.<\/h2>\n<p class=\"MsoNormal\">Rhyming dictionaries aren&#8217;t new. they are simply dictionaries that are ordered by the word written backwards. It starts with Baa and ends in Fuzz. The most famous one is probably Walkers Rhyming Dictionary. Every poet has one. Whilst they are useful, they are a bit hit and miss to use. We&#8217;ll<\/p>\n<p class=\"MsoNormal\">\u00a0be slightly more ambitious and try to give to a better rhyme. We&#8217;ll extract up to two of the final syllables of the word and match them to all \u00a0other words with the same two syllables. We are actually not getting syllables as such but the sonorant\/coda combinations. (syllables usually have an initial consonant) This seems to get a better set of rhymes<\/p>\n<p class=\"MsoNormal\">So, for Phil Factor, we have the rhymes &#8230;<\/p>\n<p class=\"MsoNormal\"><i>actor,benefactor,chiropractor,contractor,detractor,extractor,malefactor,protractor,subcontractor,tractor<\/i><\/p>\n<p class=\"MsoNormal\">&#8230; We could soon be rapping with this lot<\/p>\n<p class=\"MsoNormal\">So as to get a quick response, and keep the code manageable, we&#8217;ll create a special table for our rhyming dictionary.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">drop TABLE dbo.RhymingWords\r\nCREATE TABLE dbo.RhymingWords(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 String varchar(30) NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 PenultimateSyllable varchar(30) not null default '',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 WithoutFinalSyllable varchar(30) not null default '',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FinalSyllable varchar(30) not null default '',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT [PK_RhymingWords] PRIMARY KEY CLUSTERED\r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [String] ASC\r\n) ON [PRIMARY]\r\n) ON [PRIMARY]\r\n\u00a0\r\nGO\r\n\u00a0\r\n\u00a0\r\n\/*We start by inserting all the words from our dictionary. *\/\r\ninsert into dbo.RhymingWords (String) select String from allwords\r\n\u00a0\r\n\/* and now we extract the final syllable into its own column *\/\r\n\u00a0\r\nUPDATE RhymingWords --add in the final syllable\r\nSET\u00a0\u00a0\u00a0 WithoutFinalSyllable=LEFT(r.String, LEN(r.String)-endingSyllable),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FinalSyllable=RIGHT(r.String, endingsyllable)\r\nFROM\u00a0\u00a0 RhymingWords r\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN -- could be a monophthong, diphthong, or triphthong, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT\u00a0 String, syllable+case when PATINDEX('[aeiou]%',REVERSE(LEFT(String, LEN(String)-syllable)))=0 then 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else PATINDEX('%[aeiou][^aeiou]%',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(LEFT(String, LEN(String)-syllable))+'x') end AS Endingsyllable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT String, --pick out the last vowel-consonant transition\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PATINDEX('%[^aeiou][aeiou]%',--to get the coda\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(String))+1 AS syllable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 RhymingWords\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 PATINDEX('%[^aeiou][aeiou]%',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(String))&gt;0 ---where it is possible!\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0) f\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) g ON g.String=r.String\r\n\u00a0\r\nUPDATE RhymingWords --add in the penultimate syllable\r\nSET\u00a0\u00a0\u00a0 PenultimateSyllable=RIGHT(r.WithoutFinalSyllable, syllableIndex)\r\nFROM\u00a0\u00a0 RhymingWords r\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN\u00a0 -- could be a monophthong, diphthong, or triphthong,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT\u00a0 String, syllable+case when PATINDEX('[aeiou]%',REVERSE(LEFT(WithoutFinalSyllable, LEN(WithoutFinalSyllable)-syllable)))=0 then 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else PATINDEX('%[aeiou][^aeiou]%',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(LEFT(WithoutFinalSyllable, LEN(WithoutFinalSyllable)-syllable))+'x')end AS syllableIndex\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT String, WithoutFinalSyllable,--pick out the last vowel-consonant transition\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PATINDEX('%[^aeiou][aeiou]%',--to get the coda\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(WithoutFinalSyllable))+1 AS syllable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 RhymingWords\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 PATINDEX('%[^aeiou][aeiou]%',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(WithoutFinalSyllable))&gt;0 ---where it is possible!\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) f\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) g ON g.String=r.String\r\n\u00a0\r\n--for short words ending in vowels\r\nUPDATE RhymingWords \r\nSET\u00a0\u00a0\u00a0 FinalSyllable= right(String,patindex('%[^aeiouy]%',reverse(String)+'x')-1) from Rhymingwords\r\nwhere FinalSyllable =''\u00a0 and reverse(String)like '%[aeiouy]%'\r\n\u00a0\r\n--for words with only Ys\r\nUPDATE RhymingWords \r\nSET\u00a0\u00a0\u00a0 FinalSyllable=\u00a0 right(String,patindex('%[aeiouy]%',reverse(String)+'x')) from Rhymingwords\r\nwhere FinalSyllable ='' \r\n\u00a0\r\n--no vowels\r\nUPDATE RhymingWords \r\nSET\u00a0\u00a0\u00a0 FinalSyllable= String\r\nwhere FinalSyllable ='' \r\n\u00a0\r\n<\/pre>\n<p class=\"MsoNormal\">Now, we are in the position to create a function that takes any word and gives you the rhymes to it. You need to beware, because I have not yet programmed in automatic translation of homophones. English spelling is so inconsistent that rhyming dictionaries will never tell you all the rhymes. You have to use some artistry to get the best out of a Rhyming dictionary. &#8216;Rhyme&#8217;, rhymes with &#8216;chime&#8217;, even though the word endings are spelt differently. You need to search all the alternative spellings of the final syllable to get all the rhymes.<\/p>\n<p class=\"MsoNormal\"><i>No subtlety could, for a moment, attract her,<br \/>\nUntil she succumbed to the charms of Phil Factor<\/i><\/p>\n<p class=\"MsoNormal\">You see? No easy programming would have given you that rhyme.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\r\nIF EXISTS ( SELECT\u00a0 'found'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 sys.objects\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 name='RhymesWith' ) \r\n\u00a0\u00a0\u00a0 DROP FUNCTION RhymesWith\r\ngo\r\nCREATE FUNCTION RhymesWith (@String VARCHAR(30))\r\n\/**\r\nsummary:\u00a0 &gt;\r\n\u00a0 returns a table with the best rhymes it can with whatever valid word\r\n\u00a0 you pass to it. Because some syllables are written differently but \r\n\u00a0 sound alike (Homophonic syllables) you may need to feed in words\r\n\u00a0 that you know rhyme but have their last syllable(s) spelt differently. \r\n\u00a0 e.g. eye and spy, \r\nexample:\r\n\u00a0\u00a0\u00a0\u00a0 - code: select * from\u00a0\u00a0 dbo.RhymesWith('sand')\r\n\u00a0\u00a0\u00a0\u00a0 - code: select * from\u00a0\u00a0 dbo.RhymesWith('Phill')\r\n\u00a0\u00a0\u00a0\u00a0 - code: select * from\u00a0\u00a0 dbo.RhymesWith('technology')\r\n\u00a0\u00a0\u00a0\u00a0 \r\nreturns:\u00a0 a table of one varchar column listing all the Rhymes. \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 There once was an IT contractor\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 who went by the name of 'Phil Factor'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 He makes no apology\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for his technology\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for he's a consummate actor\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n**\/\r\nRETURNS @alternatives TABLE\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0 number INT IDENTITY(1, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY,\r\n\u00a0\u00a0\u00a0 String VARCHAR(30)\r\n\u00a0\u00a0\u00a0 )\r\nAS BEGIN\r\n\u00a0\u00a0\u00a0 DECLARE @Coda INT\r\n\u00a0\u00a0\u00a0 DECLARE @FinalSyllable VARCHAR(30)\r\n\u00a0\u00a0\u00a0 DECLARE @PenultimateSyllable VARCHAR(30)\r\n\u00a0\u00a0\u00a0 DECLARE @RestofWord VARCHAR(30)\r\n\u00a0\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @PenultimateSyllable=PenultimateSyllable,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @FinalSyllable=FinalSyllable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 RhymingWords\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 String=@String\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0 if @@Rowcount=0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0SELECT\u00a0 @Coda=PATINDEX('%[^aeiou][aeiou]%',--to get the coda\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(@String))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @Coda=@Coda+PATINDEX('%[aeiou][^aeiou]%',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(LEFT(@String,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LEN(@String)-@Coda))+'x')\r\n\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @RestOfWord=LEFT(@String, LEN(@String)-@Coda),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @FinalSyllable=RIGHT(@String, @Coda)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @Coda=PATINDEX('%[^aeiou][aeiou]%',--to get the coda\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(@RestOfWord))+1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @Coda=CASE WHEN @Coda=1 THEN 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE @Coda\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF PATINDEX('[aeiou]%',\r\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0REVERSE(LEFT(@RestOfWord, LEN(@RestOfWord)-@Coda)))&gt;0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @Coda=@Coda+PATINDEX('%[aeiou][^aeiou]%',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(LEFT(@RestOfWord,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LEN(@RestOfWord)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -@Coda))+'x')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 @PenultimateSyllable=RIGHT(@RestOfWord, @Coda)\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @FinalSyllable=''\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND REVERSE(@String) LIKE '%[aeiouy]%' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @FinalSyllable=RIGHT(@String,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PATINDEX('%[^aeiouy]%',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(@String)+'x')-1)\r\n\u00a0\r\n--for words with only Ys\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @FinalSyllable='' \r\n\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SET @FinalSyllable=RIGHT(@String,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PATINDEX('%[aeiouy]%',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(@String)+'x'))\r\n\u00a0\r\n--no vowels\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @FinalSyllable='' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @FinalSyllable=@String\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\u00a0\r\n\u00a0\u00a0\u00a0 INSERT\u00a0 INTO @alternatives (String)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 String\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 RhymingWords\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 PenultimateSyllable+FinalSyllable=@PenultimateSyllable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +@FinalSyllable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND String&lt;&gt;@String\r\n\u00a0\u00a0\u00a0 IF @@Rowcount&lt;10 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT\u00a0 INTO @alternatives (String)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 String\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 RhymingWords\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 FinalSyllable=@FinalSyllable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND String&lt;&gt;@String\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 RETURN\r\n\u00a0\u00a0 END\r\n\r\n<\/pre>\n<h2>Rap up<\/h2>\n<p>So here we are with a word-bank that allows you to cheat at Scrabble and rap, or make up doggerel. More to the point, it has illustrated, in the &#8216;permutation&#8217; function, how to use a numbers table to create a table, and the &#8216;quirky update&#8217; method of filling a table with permutation, or any other data you need. We&#8217;ve also illustrated some techniques of using the built-in, and rather primitive, character pattern-matching techniques of SQL Server.<\/p>\n<h2>A Parting Competition<\/h2>\n<p>To end up with: here is a simple competition, that I will award a Christmas prize of a $50 Amazon voucher for.<\/p>\n<p>Given that Scrabble is scored to the following table:<\/p>\n<ul>\n<li>How fast can you score all common words according to their Scrabble scores, so as to list them in order.?<\/li>\n<li>Find all the words that can&#8217;t ever be used in Scrabble? (Blank tiles can mean any letter).\u00a0\u00a0 Scrabble contains&#8230;\n<ul>\n<li>\u00a0\u00a0\u00a0 2 blank tiles (scoring 0 points) that can represent any letter<\/li>\n<li>\u00a0\u00a0\u00a0 1 point: <b>E<\/b> \u00d712, <b>A<\/b> \u00d79, <b>I<\/b> \u00d79, <b>O<\/b> \u00d78, <b>N<\/b> \u00d76, <b>R<\/b> \u00d76, <b>T<\/b> \u00d76, <b>L<\/b> \u00d74, <b>S<\/b> \u00d74, <b>U<\/b> \u00d74<\/li>\n<li>\u00a0\u00a0\u00a0 2 points: <b>D<\/b> \u00d74, <b>G<\/b> \u00d73<\/li>\n<li>\u00a0\u00a0\u00a0 3 points: <b>B<\/b> \u00d72, <b>C<\/b> \u00d72, <b>M<\/b> \u00d72, <b>P<\/b> \u00d72<\/li>\n<li>\u00a0\u00a0\u00a0 4 points: <b>F <\/b>\u00d72, <b>H<\/b> \u00d72<b>, V<\/b> \u00d72, <b>W<\/b> \u00d72, <b>Y<\/b> \u00d72<\/li>\n<li>\u00a0\u00a0\u00a0 5 points: <b>K<\/b> \u00d71<\/li>\n<li>\u00a0\u00a0\u00a0 8 points: <b>J<\/b> \u00d71, <b>X<\/b> \u00d71<\/li>\n<li>\u00a0\u00a0\u00a0 10 points: <b>Q<\/b> \u00d71, <b>Z<\/b> \u00d71<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In which Phil decides to use a table consisting of all the common words in English to explore ways of cheating at Scrabble and writing doggerel using SQL Server. He then issues a SQL challenge.&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4151,4252],"coauthors":[6813],"class_list":["post-758","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/758","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=758"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/758\/revisions"}],"predecessor-version":[{"id":73050,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/758\/revisions\/73050"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=758"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=758"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=758"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=758"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}