{"id":2268,"date":"2006-01-29T14:59:00","date_gmt":"2006-01-29T14:59:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/if-you-go-down-to-the-woods-today\/"},"modified":"2017-07-10T17:09:30","modified_gmt":"2017-07-10T17:09:30","slug":"if-you-go-down-to-the-woods-today","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/if-you-go-down-to-the-woods-today\/","title":{"rendered":"If you go down to the woods today&#8230;"},"content":{"rendered":"<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true \">--On the drawing of  teddy-bears\r\n\/*\r\nI have been gently reproached for making my blog too developer-oriented, and geeky; thereby making managers feel neglected. We therefore introduce Teddy, our foray into unashamed  nostalgia in the days of 80x25 character terminals. In those days, immense effort was expended in drawing pictures in this unpromising medium\r\n*\/\r\n\r\nCREATE   PROCEDURE spTeddy\r\n@teddy VARCHAR(8000)=NULL OUTPUT \r\nAS\r\n\r\nSELECT @teddy='\r\n\r\n       (.)_(.)       \r\n       { . . }       \r\n        \\_*_\/        \r\n      \/ .   . \\      \r\n     {_}     {_}     \r\n       |     |       \r\n      (_}- -{_}      \r\n'\r\n\r\nGO\r\n\r\n--which we can then display with....\r\nDECLARE @teddy VARCHAR(8000)\r\nEXECUTE spTeddy @teddy=@teddy OUTPUT\r\nSELECT @teddy\r\n\r\n\r\n\/* at this point I was going to introduce a competition to turn teddy upside down, but in experimenting with the concept I decided it was far too simple for our razor-sharp audience.\r\nYou may think... *\/\r\nDECLARE @teddy VARCHAR(8000)\r\nEXECUTE spTeddy @teddy=@teddy OUTPUT\r\nSELECT Reverse(@teddy)\r\nGO\r\n\/* will do the trick. Well almost, but the results need tidying up\r\nSo we introduce a helping function that reverses some of the characters*\/\r\nCREATE FUNCTION dbo.ufsReplaceChars\r\n( \r\n@String VARCHAR(8000),--the string within which you want the characters substituted\r\n@change VARCHAR(200), --the characters\r\n@ReplaceWith VARCHAR(200)--the substitutes\r\n) \r\nRETURNS VARCHAR(8000) AS\r\n\r\nBEGIN \r\nDECLARE @ModifiedString VARCHAR(8000)\r\nIF Len(@ReplaceWith)&lt;Len(@change)\r\n SELECT @change=@change+Space(Len(@change)-Len(@ReplaceWith))\r\nIF (Coalesce(@String,'') ='' )\r\n SELECT @ModifiedString=@String\r\nELSE\r\n WHILE Len(@String)&gt;0\r\n\r\n  IF CharIndex (Substring (@String,1,1), @change) = 0 \r\n   BEGIN \r\n   SELECT @ModifiedString = Coalesce(@ModifiedString,'')\r\n     +Substring (@String,1,1)\r\n   SELECT @String=Substring (@String,2,Len(@String)-1)\r\n   END \r\n  ELSE\r\n   BEGIN\r\n   SELECT @ModifiedString = Coalesce(@ModifiedString,'')\r\n    +Substring (@ReplaceWith,CharIndex (Substring (@String,1,1), @change),1)\r\n   SELECT @String=Substring (@String,2,Len(@String)-1)\r\n   END\r\nRETURN (@ModifiedString) \r\nEND\r\n\r\n\r\nDECLARE @teddy VARCHAR(8000)\r\nEXECUTE spTeddy @teddy=@teddy OUTPUT\r\nSELECT Replace(dbo.ufsReplaceChars(Reverse(@teddy),'_)({}.','\u00af()}{''')\r\n,Char(10)+Char(13),'\r\n')\r\n\r\n\/*\r\n      {\u00af}- -{\u00af)      \r\n       |     |       \r\n     {\u00af}     {\u00af}     \r\n      \\ '   ' \/      \r\n        \/\u00af*\u00af\\        \r\n       { ' ' }       \r\n       (')\u00af(')      \r\n\r\nAll very satisfying.\r\n\r\nASCII art is one of the more esoteric parts of Computer science. Since Wikipedia has covered the subject in excellent detail, I need only refer you there http:\/\/en.wikipedia.org\/wiki\/ASCII_art. Someone has even recoded the entire Matrix movie as ASCII.\r\n\r\nYou'll wonder, as I did, whether one could introduce some compression into the storage of ASCII art. Some ASCII art can get pretty big in size. \r\nThen I got to thinking that I hadn't actually seen a good string compression function around for SQL server\r\n\r\nASCII art generally uses a tiny subset of the ASCII range so storing it in standard ASCII is very uneconomic. . In fact the Teddy is only 14 characters\r\n*\/\r\ncharacter  freq.       \r\n---------- ----------- \r\n' '        111\r\nchar(10)   9\r\nchar(13)   9\r\n'_'        7\r\n'.'        6\r\n'}'        5\r\n'{'        4\r\n'('        3\r\n')'        2\r\n'\/'        2\r\n'\\'        2\r\n'|'        2\r\n'-'        2\r\n'*'        1\r\n\r\n\/* we can determine this using another helper function that looks at the character count *\/\r\nGO\r\nCREATE  FUNCTION dbo.uftvCharacterFrequency\r\n\/* produces a character count of all the characters in a string\r\nas a frequency table*\/\r\n(\r\n @String VARCHAR(8000)\r\n)\r\nRETURNS\r\n@Results TABLE\r\n(\r\n [character] VARCHAR(10),\r\n [freq.] INT\r\n)\r\nAS\r\nBEGIN\r\nDECLARE @ii INT\r\nDECLARE @iiMax INT\r\nDECLARE @temp TABLE (Thechar CHAR(1))\r\nSELECT @ii=1, @iiMax=Len( @String)\r\nWHILE @ii&lt;=@iiMax\r\n BEGIN\r\n INSERT INTO @temp SELECT Substring( @String,@ii,1)\r\n SELECT @ii=@ii+1\r\n END\r\nINSERT INTO @Results\r\nSELECT [character]=\r\n CASE \r\n     WHEN Ascii(Thechar)&gt;=32 THEN ''''+Thechar+'''' \r\n ELSE 'char('+Convert(VARCHAR(3),Ascii(Thechar))+')' END,\r\n[freq.]= Count(*) FROM @temp GROUP BY Thechar ORDER BY Count(*) DESC\r\nRETURN\r\nEND\r\n\r\n\/* we can then see what the character frequency of Teddy is with the following code *\/\r\nDECLARE @teddy VARCHAR(8000)\r\nEXECUTE spTeddy @teddy=@teddy OUTPUT\r\nSELECT uftvCharacterFrequency.character, uftvCharacterFrequency.[freq.] FROM dbo.uftvCharacterFrequency(@teddy)\r\n\r\n\/* So The competition here is to provide the best compression\/Decompression algorithm for reducing the storage requirements for typical ASCII art such as teddy.\r\nThis doesn't have to be complex. Even replacing every group of five spaces with a special character reduces Teddy's storage by 38.8%\r\n\r\n*\/ <\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8211;On the drawing of teddy-bears \/* I have been gently reproached for making my blog too developer-oriented, and geeky; thereby making managers feel neglected. We therefore introduce Teddy, our foray into unashamed nostalgia in the days of 80&#215;25 character terminals. In those days, immense effort was expended in drawing pictures in this unpromising medium *\/&#8230;&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":[2],"tags":[],"coauthors":[],"class_list":["post-2268","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2268","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=2268"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2268\/revisions"}],"predecessor-version":[{"id":71654,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2268\/revisions\/71654"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2268"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2268"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2268"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}