{"id":1054,"date":"2010-12-20T00:00:00","date_gmt":"2010-12-20T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-parodist-a-sql-server-application\/"},"modified":"2021-09-29T16:21:55","modified_gmt":"2021-09-29T16:21:55","slug":"the-parodist-a-sql-server-application","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-parodist-a-sql-server-application\/","title":{"rendered":"The Parodist: A SQL Server Application"},"content":{"rendered":"
\n

The Parodist, which I’ll be describing here, is a small database application that creates parodies of whatever book you feel inclined to feed into it. I offer you this little script for your amusement as a Christmas present. It may even deliver you your next homework essay in postmodernism, or your next marketing White-paper.<\/p>\n

This could be quite a productivity tool. Who would quarrel with these profound words about XML and PHP?<\/p>\n

An element may also be defined using annotated schema. A plan is expensive or inexpensive. The feature can be indexed. The best choice of XML Data type still remains.<\/em><\/p>\n

Complete each box for two expressions that are simple to implement. When your data is highly structured, XML data for example, users may want to use the INSERT workload and between 10 and 2. The LSNs still exist in a PHP resource corresponding to the change table after the load on the production workload.<\/em><\/p>\n

You can refer to the subtype table has a very simple example and it is divided in groups of 64K records called a segment. Note the first and last names of actual companies and products mentioned herein may be used in Example: View and a set is best introduced by contrasting it with a sum measure on the XML schema collection myCollection” within relational schema for your business needs. None of these conditions are true:<\/em><\/p>\n

For more information about thread settings In addition to that log records to be configured at the leaves and aggregate several partitions at the time of the sections Relevant Wait Types for Subscriptions SQL Server 2008 Analysis Services will scan and aggregate the segments of one partition at a small number of concurrent threads that perform the SWITCH. Optionally a primary key from the example below.<\/em><\/p>\n

… except that it is gibberish produced by The ‘Parodist’. Still, it is compelling gibberish, isn’t it.<\/p>\n

This might look to you something like Homer’s Iliad, but it isn’t ….<\/p>\n

Nor yet e’en so Achilles let his counsel take<\/em>
\n who should strike the bird <\/em>
\n As less in skill not one was absent;<\/em>
\n nor of his lofty vessel’s prow The dark whirlwind’s force<\/em>
\n as this wine we pour their hearts’ best blood <\/em>
\n Theirs and their fam’d Allies Who freely in thy house<\/em>
\n receiv’d For twenty days the heav’nly Maid Daughter of Cisseus<\/em>
\n sage Antenor’s wife By Trojans built of fir-trees fell’d <\/em>
\n and overlaid the roof With rushes mown from off a fly;<\/em>
\n Its course she so directed that it struck but drove not through<\/em>
\n For near the warlike Mysians next Antilochus From Mermerus and Phalces<\/em>
\n stripp’d their armour spoil’d shalt make thy pray’r<\/em><\/p>\n

I seem to be in the habit of producing frivolous applications at this time of year. Once, I did a SQL Server version of the classic ‘Waffle Generator’. Several readers asked for something just as clever but less restricting, so that one could get waffle from any text. Fine, I thought, time to implement a finite-state Markov chain with stationary transition possibilities. Actually, the process isn’t as scary as the name. The algorithm was described in 1948, long before personal computers, by Claude Shannon, first in The Bell System Technical Journal, Volume XXVII, No. 3 & 4, and then in The Mathematical Theory of Communication ( ISBN: 9780252725487). The best description I know of is in Jon Bentley’s ‘Programming Pearls’, in the chapter ‘Strings of Pearls’.<\/p>\n

It may seem surprising that a technique like this preceded the Age of Information Technology, but we were aware of them\u00a0whilst they were too long-winded to implement by a manual process. One of the great experiences of IT in the 1970s was the sheer pleasure of trying out ideas that were known about, but were impractical until computers were cheap enough to have in the home. With pencil and paper, these were mere party games, but with computers, they became algorithms.<\/p>\n

If you had the patience of a saint, you could do this manually. You would open a book at random, put your finger on the page randomly, and track along to the start of the next sentence, You have the starting word of a sentence: Then, you would find the second word in another sentence that started with that initial word. Once you had the third word, by proceeding likewise, you would be ready to start. You find, at random, a sentence containing the previous two words, adjacent and in the same order; and you take this third word and append it. As authors tend to have mannerisms, and use favourite phrases, this technique seems to pick them up: One gets clich\u00e9s strung together. The more text you have the better the parody. with small amounts, it just sticks to the sentences it selects, but the richer the text bank, the more creative it gets. See what it does with Mark Twain’s Huckleberry Finn<\/p>\n

“Why it’s perfectly ridiculous, but I couldn’t cut the bullet and twice I seen where one of his pocket. Then it was here again for I was used to rip around so and swallow the sawdust so it was about done loading and pretty soon I noticed the king made me creep. <\/em><\/p>\n

They was off working for the fields; and Jim’s eyes bugged out and says: “Why yonder he is, and if we’d a’ whooped him over to t’other village without any handle; and a roundabout and pants of his father would die first”. But Tom said “Now for the shore, a little twenty-foot yard. My! you ought to seen old Henry the Eight; when he said the duke’s room, and hustling away and leave my sisters with them”. A witch PIE in Jim’s pan and we got some of the river but didn’t look at it. What could he ever undertook that Tom and I’ll do it. “<\/em><\/p>\n

You might have thought that such a powerful parodist as this would consist of a great deal of code. No, actually, it doesn’t because it is doing what SQL Server does best, which is to search and sort vast quantities of data. So without further introduction here is the code, which should speak for itself. You can find the original source in the downloads at the bottom of the article.<\/p>\n

IF OBJECT_ID(N'WordChop') IS NOT NULL \r\n\u00a0 DROP FUNCTION WordChop\r\nGO\r\nCREATE FUNCTION [dbo].[WordChop] (@string VARCHAR(MAX))\r\nRETURNS @Results TABLE (SequenceNumber INT IDENTITY(1, 1),\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 Item VARCHAR(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 Terminator CHAR(1))\r\n\u00a0\/**\r\nsummary:\u00a0\u00a0 >\r\nThis Table-valued function takes any text as a parameter and splits it into its constituent words, passing back the order in which they occured and also the character (usually space) that terminated the word. \r\nAuthor: Phil Factor\r\nRevision: 1.0\r\ndate: 20 Dec 2010\r\nexample:\r\n\u00a0\u00a0\u00a0\u00a0 - code: SELECT * FROM WordChop('this tests stuff. Will it work?')\r\n\u00a0\u00a0\u00a0\u00a0 - code: SELECT * FROM WordChop('this ------- tests it again; Will it work ...')\r\nreturns:\u00a0\u00a0 >\r\nTable of SequenceNumber, Item (word) and Terminating character.\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 \r\nAS \r\nBEGIN\r\n\u00a0 DECLARE @LengthOfString INT,--the length of the entire string\r\n\u00a0\u00a0\u00a0 @Start INT, --the index from the Cursor to the start of the next word\r\n\u00a0\u00a0\u00a0 @Cursor INT,--the current index into the entire string\r\n\u00a0\u00a0\u00a0 @LengthOfWord INT --the length of the current word\r\n\u00a0 SELECT @Cursor = 1, @LengthOfString = LEN(@string);\r\n\u00a0 WHILE @Cursor < @LengthOfString \r\n\u00a0\u00a0\u00a0 BEGIN --first we find the transition to the start of the word\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @start = PATINDEX('%[^A-Za-z0-9][A-Za-z0-9]%',\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 ' ' + SUBSTRING(@string, @Cursor, 100)) - 1;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 IF @start < 0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BREAK\u00a0 --we've done them all if we cant find any more\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 --otherwise we dind the transition point to the end of the string\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @LengthOfWord = PATINDEX('%[^A-Z''a-z0-9-]%',\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 SUBSTRING(@string, @Cursor + @start + 1, 50) + ' ')\u00a0 ;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 --put the string and the Terminating character into the column\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT INTO @results (Item, Terminator)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT SUBSTRING(@string, @Cursor + @start, @LengthOfWord),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUBSTRING(@string, @Cursor + @start + @LengthOfWord, 1);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @Cursor = @Cursor + @Start + @LengthOfWord + 1;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 --bump the cursor on\r\n\u00a0\u00a0\u00a0 END\r\n\u00a0 RETURN\r\nEND\r\nGO\r\n\u00a0\r\nIF OBJECT_ID(N'LoadBook') IS NOT NULL \r\n\u00a0 DROP PROCEDURE LoadBook\r\nGO\r\nCREATE PROCEDURE LoadBook\r\n\u00a0 @NameAndPathOnServer VARCHAR(255)\r\n\/**\r\nsummary:\u00a0\u00a0 >\r\nThis Procedure loads a file into a variable, chops it up into its constituent words and loads it into a global temporary table for subsequent analysis. It creates a second global temporary table to hold the unique list of words used, and the number of times each word was used. In the analysis table, it records the ids of the two preceeding words in the sentence, or zero if there aren't any. \r\nAuthor: Phil Factor\r\nRevision: 1.0 \r\ndate: 20 Dec 2010\r\nexample:\r\n\u00a0\u00a0\u00a0\u00a0 - code: EXECUTE LoadBook 'MyDirectory\\KJB.txt'\r\n\u00a0\u00a0\u00a0\u00a0 - code: EXECUTE LoadBook 'MyDirectory\\Dracula.txt'\r\n\u00a0\u00a0\u00a0\u00a0 - code: EXECUTE LoadBook 'MyDirectory\\SherlockHolmes.txt'\r\n\u00a0\u00a0\u00a0\u00a0 - code: EXECUTE LoadBook 'MyDirectory\\CanterburyTales.txt'\r\n\u00a0\u00a0\u00a0\u00a0 - code: EXECUTE LoadBook 'MyDirectory\\HomersIliad.txt'\r\n\u00a0\u00a0\u00a0\u00a0 - code: EXECUTE LoadBook 'MyDirectory\\CompleteWilliamShakespeare.txt'\r\n\u00a0\u00a0\u00a0\u00a0 - code: EXECUTE LoadBook 'MyDirectory\\HuckleberryFinn.txt'\r\n\u00a0\u00a0\u00a0\u00a0 - code: EXECUTE LoadBook 'MyDirectory\\BrerRabbit.txt'\r\n\u00a0\r\nreturns:\u00a0\u00a0 >\r\n0 if successful.\r\n**\/\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nAS \r\nDECLARE @LotsOfText VARCHAR(MAX),\r\n\u00a0\u00a0\u00a0 @Command NVARCHAR(MAX)\r\n\/* We want to read the text file in. Microsoft makes it very hard to use the OpenRowset Bulk with a supplied parameter as a local variable but are we at all discouraged? No Sir, we do a bit of sp_execute.*\/\u00a0\u00a0\u00a0 \r\n\u00a0\r\nSELECT @Command = 'SELECT\u00a0 @Filecontents = BulkColumn\r\nFROM\u00a0\u00a0\u00a0\u00a0 OPENROWSET(BULK ''' + @NameAndPathOnServer + ''', SINGLE_BLOB) AS x'\r\nEXECUTE master..sp_executeSQL @Statment = @Command,\r\n\u00a0\u00a0\u00a0 @params = N'@FileContents VARCHAR(MAX) OUTPUT',\r\n\u00a0\u00a0\u00a0 @Filecontents = @LotsOfText OUTPUT;\r\n\/* Read each word into a table (we make it a global temporary table as we don't want to keep it for ever!*\/\r\nIF EXISTS (\r\n\u00a0\u00a0\u00a0 SELECT *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 tempDB.sys.tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 name LIKE N'##Word' )\r\n\u00a0\u00a0\u00a0 DROP TABLE ##Word;\r\n\/* This gives each word in order, and records the non-letter character that terminates the word. the identity of the two preceding words is added later as is the unique id of the word. *\/\r\nCREATE TABLE ##Word \r\n\u00a0 (SequenceNumber INT NOT NULL PRIMARY KEY,\r\n\u00a0\u00a0 Item VARCHAR(100) NOT NULL,\r\n\u00a0\u00a0 Terminator CHAR(1) NOT NULL,--character Terminating the word\r\n\u00a0\u00a0 Terminating CHAR(1) NOT NULL\r\n\u00a0\u00a0 DEFAULT 'n',--is it Terminating a sentence?\r\n\u00a0\u00a0 word_ID INT NULL,\r\n\u00a0\u00a0 PreviousWord_ID INT NOT NULL DEFAULT 0,\r\n\u00a0\u00a0 PluPreviousWord_ID INT NOT NULL DEFAULT 0);\r\n\/* now we put all the words from the file, in order, into this table *\/\u00a0 \r\nINSERT INTO ##Word (SequenceNumber, Item, Terminator)\r\n\u00a0\u00a0\u00a0 SELECT SequenceNumber, Item, Terminator\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 WordChop(@LotsOfText);\r\n-- clear out any previous work of literature\r\nIF EXISTS ( SELECT * FROM\u00a0\u00a0\u00a0 tempDB.sys.tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 name LIKE N'##UniqueWords' )\r\n\u00a0\u00a0\u00a0 DROP TABLE ##UniqueWords;\r\n--and recreate the table\r\nCREATE TABLE ##UniqueWords \r\n\u00a0 (Word_ID INT IDENTITY(1, 1) PRIMARY KEY,\r\n\u00a0\u00a0 Word VARCHAR(100) NOT NULL,\r\n\u00a0\u00a0 wordCount INT);\r\n--we will want the indexes of the two previous words to add to the table.\u00a0\u00a0 \r\nINSERT INTO ##UniqueWords (Word, wordCount)\r\n\u00a0\u00a0\u00a0 SELECT Item, COUNT(*) FROM\u00a0\u00a0\u00a0 ##Word\r\n\u00a0\u00a0\u00a0 GROUP BY Item\u00a0 ORDER BY Item;\r\n--now we indicate whether the word terminates a sentence.\r\nUPDATE ##Word\r\n\u00a0\u00a0\u00a0 SET Word_ID = ##UniqueWords.Word_ID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Terminating = CASE WHEN Item IN ('Mrs', 'Ms', 'Mr') THEN 'n'\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 WHEN ISNUMERIC(Item) = 1 THEN 'n'\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 WHEN Terminator IN ('.', ':') THEN 't'\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 ELSE 'n'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\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 FROM\u00a0\u00a0\u00a0 ##Word INNER JOIN ##uniqueWords ON Word = Item;\r\n\u00a0\u00a0\u00a0 \r\nDECLARE \r\n\u00a0\u00a0\u00a0 @PreviousWord_ID INT,\r\n\u00a0\u00a0\u00a0 @PluPreviousWord_ID INT,\r\n\u00a0\u00a0\u00a0 @Word_ID INT,\r\n\u00a0\u00a0\u00a0 @Terminating CHAR(1)\r\n\u00a0\r\nSELECT @PreviousWord_ID =0,@PluPreviousWord_ID =0,\u00a0 @Word_ID =0;\r\n--this is wicked, but it is fast and we have, after all, defined a primary key \r\nUPDATE ##word\r\n\u00a0 SET \r\n\u00a0 @PluPreviousWord_ID = PluPreviousWord_ID=CASE when @Terminating='t' THEN 0 ELSE @Previousword_ID END,\r\n\u00a0 @PreviousWord_ID = previousWord_ID=CASE when @Terminating='t' THEN 0 ELSE @word_ID END,\r\n\u00a0 @Word_ID=Word_ID,\r\n\u00a0 @Terminating=Terminating;\r\n\u00a0\r\nCREATE INDEX WhatsGoneBefore ON ##Word(PluPreviousWord_ID, PreviousWord_ID);\r\n\u00a0 \r\nGO\r\n\u00a0\r\nIF OBJECT_ID(N'CreateSentence') IS NOT NULL \r\n\u00a0 DROP PROCEDURE\u00a0 CreateSentence\r\nGO\r\nCREATE PROCEDURE CreateSentence\r\n\u00a0 @TheSentence VARCHAR(MAX) OUTPUT\r\n\/**\r\nsummary:\u00a0\u00a0 >\r\nThis Procedure creates a sentence from the ##words table. It does this by choosing a sentence at random from the bank, and takes the unitial word. Then it finds, randomly, a second word that follows that first word at the start of a sentence. \r\nAfter it has got a sentence start, it then repeatedly finds, randomly, a word that follows the two preceding ones until it reaches a word that is a sentence end. then it quits.\r\nAuthor: Phil Factor\r\nRevision: 1.0 \r\ndate: 20 Dec 2010\r\nexample:\r\n\u00a0\u00a0\u00a0\u00a0 - code: >\r\nDECLARE @Sentence VARCHAR(MAX)\r\nEXECUTE CreateSentence @Sentence OUTPUT\r\nSELECT @Sentence\r\nreturns:\u00a0\u00a0 >\r\n0 if successful. Returns the sentence as an output variable\r\n**\/\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nAS \r\nSET NOCOUNT on\r\nDECLARE @PreviousWord_ID INT,\r\n\u00a0\u00a0\u00a0 @PluPreviousWord_ID INT,\r\n\u00a0\u00a0\u00a0 @SentenceStart INT,\r\n\u00a0\u00a0\u00a0 @Word VARCHAR(100),\r\n\u00a0\u00a0\u00a0 @Iterator INT,\r\n\u00a0\u00a0\u00a0 @SentenceEnd CHAR(1),\r\n\u00a0\u00a0\u00a0 @Word_ID INT,\r\n\u00a0\u00a0\u00a0 @Terminator CHAR(1);\r\nDECLARE @SentenceOrder TABLE --used to store the sentence as it is assembled\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (sequence_ID INT IDENTITY(1, 1),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0word_ID INT NOT NULL,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 word VARCHAR(100),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Terminator CHAR(1) NOT NULL);\r\nIF NOT EXISTS (\r\n\u00a0\u00a0\u00a0 SELECT *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 tempDB.sys.tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 name LIKE N'##Word' )\r\n\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0 RAISERROR('I''m sorry but you will need to load a text bank to do this using the LoadBook procedure',16,1)\r\n\u00a0\u00a0\u00a0 RETURN 1\r\n\u00a0\u00a0\u00a0 END\r\n\u00a0\r\nSELECT @Iterator=20 ;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nWHILE @PreviousWord_ID IS NULL and @Iterator>0\r\n\u00a0\u00a0\u00a0 BEGIN--eliminate one word sentences\r\n\u00a0\u00a0\u00a0 DELETE\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @SentenceOrder;\r\n\u00a0\u00a0\u00a0 SELECT TOP 1 --choose a sentence-starting word at random\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @PluPreviousWord_ID = Word_ID, @Word = Item,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Terminator = Terminator\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 ##Word\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 PreviousWord_ID = 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND PluPreviousWord_ID = 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Terminating = 'n'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY NEWID();\r\n\u00a0\u00a0\u00a0 INSERT INTO @SentenceOrder (word_ID, word, Terminator)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @PluPreviousWord_ID, @word, @Terminator;\r\n\u00a0\u00a0\u00a0 SELECT TOP 1 --choose a word that follows the one we have found at the\r\n\u00a0--start of a sentence.\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @previousWord_ID = Word_ID, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Word = Item, @Terminator = Terminator\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 ##Word\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 PreviousWord_ID = @PluPreviousWord_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND PluPreviousWord_ID = 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Terminating = 'n'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY NEWID();\r\n\u00a0\u00a0\u00a0\u00a0 SELECT @Iterator=@Iterator-1;\u00a0 --so we haver an exit in a sentence-less piece of literature \r\n\u00a0\u00a0\u00a0 END\r\n\u00a0IF @Iterator=0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RAISERROR ('Words table unprocessed, or wordbank without punctuation',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16,1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN 1 ;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\nINSERT INTO @SentenceOrder (word_ID, Word, Terminator)\r\n\u00a0\u00a0\u00a0 SELECT @PreviousWord_ID, @Word, COALESCE(@Terminator, ' ')\r\n\/* now keep building up words until we reach the end of a sentence *\/\r\nSELECT @Iterator = 100, @SentenceEnd = 'n';\r\nWHILE @SentenceEnd = 'n' AND @Iterator > 0\r\n\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0 SELECT TOP 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Word_ID = Word_ID, @Word = Item, @Terminator = Terminator,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @SentenceEnd = Terminating\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 ##Word\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 PreviousWord_ID = @PreviousWord_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND PluPreviousWord_ID = @PluPreviousWord_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY NEWID() ;\r\n\u00a0\u00a0\u00a0 IF @@Rowcount = 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RAISERROR ('this is impossible; but has happened with words: %o and %o',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16,1,@PreviousWord_ID,@PluPreviousWord_ID)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BREAK ;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END\r\n\u00a0\u00a0\u00a0 INSERT INTO @SentenceOrder (word_ID, Word, Terminator)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @Word_ID, @Word, @Terminator;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @PluPreviousWord_ID = @PreviousWord_ID;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @PreviousWord_ID = @Word_ID;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @Iterator = @Iterator - 1;\r\n\u00a0\u00a0\u00a0 END\r\nSELECT @TheSentence = (\r\n\u00a0\u00a0\u00a0 SELECT CAST(word + CASE WHEN Terminator IN ('.', ':',';', '-', '\"', '''')\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 THEN Terminator + ' ' ELSE ' ' END AS VARCHAR(MAX))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 @SentenceOrder\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY Sequence_ID FOR XML PATH('')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\nGO\r\n\u00a0\r\nIF OBJECT_ID(N'CreateSomeSentences') IS NOT NULL \r\n\u00a0 DROP PROCEDURE\u00a0 CreateSomeSentences\r\nGO\r\nCREATE PROCEDURE CreateSomeSentences\r\n@HowMany INT =null\r\n\/**\r\nsummary:\u00a0\u00a0 >\r\nThis Procedure creates a number of sentences from the ##words table. It calls the routine that does the real work 'CreateSentence' repeatedly until it has gotten the correct number of sentences. The default is 16.\r\nAuthor: Phil Factor\r\nRevision: 1.0 \r\ndate: 20 Dec 2010\r\nexample:\r\n\u00a0\u00a0\u00a0\u00a0 - code: Execute CreateSomeSentences\r\n\u00a0\u00a0\u00a0\u00a0 - code: Execute CreateSomeSentences 24\r\nreturns:\u00a0\u00a0 >\r\n0 if successful. Returns the sentence as an output variable\r\n*\/\r\nas\r\nDECLARE @Sentence VARCHAR(MAX)\r\nDECLARE @Text VARCHAR(MAX)\r\nDECLARE @Iterator INT\r\nSELECT @Iterator = COALESCE(@HowMany,16)\r\nIF NOT EXISTS (\r\n\u00a0\u00a0\u00a0 SELECT *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 tempDB.sys.tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 name LIKE N'##Word' )\r\n\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0 RAISERROR('I''m sorry but you will need to load a text bank to do this using the LoadBook procedure',16,1)\r\n\u00a0\u00a0\u00a0 RETURN 1\r\n\u00a0\u00a0\u00a0 END\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\nWHILE @Iterator > 0\r\n\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0 EXECUTE CreateSentence @Sentence OUTPUT;\r\n\u00a0\u00a0\u00a0 SELECT @Iterator = @Iterator - 1,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Text = COALESCE(@Text + ' ', '') \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + CASE WHEN RAND() < 0.3\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN CHAR(13) + CHAR(10)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE ''\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END + @Sentence;\r\n\u00a0\u00a0\u00a0 END\r\nSELECT @Text;\r\nGO\r\n\/* to use this application, you just create a database called 'parodist'. Then you load the book, or bank of sentences you want to parody like this:\r\nEXECUTE LoadBook 'MyDirectory\\DraculaAndFrankenstein.txt'\r\n--Then you just get the parody text by calling\r\nEXECUTE CreateSomeSentences\r\n\u00a0\r\nAnd here is the result of mixing two original wordbanks!\r\n\u00a0\r\n'Then we made a few hours before.\u00a0 I sent them over and read Sacred to the drawing room where we had all gathered round the room; he came out from under the eye can have departed forever--that the brightness of a servant, a condition precarious, and full today with a gesture placed us each in position.\u00a0 In accordance with instructions and keys left in pretty good time but when we closed the door was unlocked and now I think I put him in his sight.\u00a0 The spirits who assist my vengeance in his dressing gown and Mr. Morris Lord Godalming tells me her teeth are sharper and at a loss for words to describe--gigantic in stature yet uncouth and inarticulate sounds which broke from all littleness of feeling and delight than in public for there must be brave of heart. I opened another door in orthodox fashion putting the key in the long hours and felt somewhat safer in all. We can only trust in the firelight and they will soon think that of all these things yet he may purchase does not understand even in her.\u00a0 I visited him myself.\u00a0 Hear me! Let me tell you that the Honfoglalas was completed there And it is the second time was solely directed towards the south.\u00a0 \r\nBut now he make in the room gently opened the door and seeing where she had not yet begun to take into this place or the study and I have confessed myself guilty of the fair moon for a moment on the floor.\u00a0 In fine if he go in lest I should have been thinking that tomorrow will see the tension of her head and bore all the way that I at length she formed her determination.\u00a0 The starry sky the sea it was it indeed such spiritual intensity that her imagination is beginning to increase my misery you will but take the glass.\u00a0 I had only the solid advantage of their miserable fare.\u00a0 I had visited her and whispers to me in he threw from time to save poor Lucy.\u00a0 \r\nThose are as fixed as fate and my loud unrestrained heartless laughter frightened and amazed me somewhat.\u00a0 He was sweetly courteous. \r\n'\r\n*\/\r\n\u00a0\r\n<\/pre>\n
If you need any more convincing, here is Shakespeare, put through the mangle<\/p>\n
“Here’s ado to make my peace. I pray you tarry; longer I shall make it flame again; for more I prithee more. As by his small light of discretion that he should escape; For though she’s as fartuous a civil doctor which did put his cause being her uncle Rivers; Talk’d how I do not know DROMIO OF SYRACUSE. My lord Northumberland What says his Majesty bade me from the rebels.<\/em><\/p>\n
In honest civil godly company for speedy aid; wherein our dearest friend Prejudicates the business is not a son being twain. These are they. Ay sir; a word I pray. Enough sweet Suffolk thou torment’st thyself; And I am glad I am not well; What has he that did haunt me still. Take that winter kill it.<\/em><\/p>\n
Mine such as doth an inland man; one fire drives out one fire; one nail, one nail, one nail; Rights by me be brief my practice quit his pain. No leisure had he none but libertines delight in them is hereabout And cannot help him; young and though his false finger have profan’d the ring And look upon The Queen; come not by But stay here for defence For courage mounteth with occasion.<\/em><\/p>\n
Love they flatter me; that can I fortune to the Capitol. Peter didst ever hear better. That will not be wink’d at how shall I be afeard. Pardon dear madam. But if he were dead I’ll speak with Coriolanus.“<\/span><\/em><\/p>\n
And what about Brer Rabbit?<\/p>\n
“Folks tell um fer me bittle. Whar I ain’t gwine ter stan’ inside en Brer Rabbit he tuck’n stan’ on one side. What are they going to Uncle Remus’s anger was all pretence but he wuz gwine long down de road. I done say I got you dis night en ef you’d a’ bin mighty smart man.<\/em><\/p>\n
Dat’s so en Brer Rabbit he kotch holt er de flo’ un git all de folks on dis a-way you’ll des keep yo’ eye on de trees look like it mought work. I wonder who put um in de min’ Brer Wolf run fas’ Brer Rabbit en Mr.Man cut tree down. Skin swink skin stink skin swivel. Wid dat Brer Rabbit sezee w’ich ef he don’t year no complaints fum me.”<\/em><\/p>\n
So I leave you to do your own experimentation, fuelled perhaps from\u00a0 the vast banks of text in Project Gutenberg<\/a>, or the rich comedy of marketing papers.\u00a0 Make sure that it\u00a0consists of \u00a0pure sentences terminated by full-stops (periods). Take out all extraneous\u00a0text. Try experiments such as mixing two different authors. Why not change the algorithm, or try\u00a0another variation of the Markov algorithm. After all,\u00a0SQL Server Developments can, and occasionally should, be fun.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Every year, we ask Phil Factor to celebrate the holiday season with an article on SQL Server Programming that is fun. This year, he responded with ‘The Parodist’. This is a SQL Server application, the like of which I doubt if you’ve seen before. <\/p>\n
…<\/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-1054","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\/1054","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=1054"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1054\/revisions"}],"predecessor-version":[{"id":78278,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1054\/revisions\/78278"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1054"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1054"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1054"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1054"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}