|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 |
--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 80x25 character terminals. In those days, immense effort was expended in drawing pictures in this unpromising medium */ CREATE PROCEDURE spTeddy @teddy VARCHAR(8000)=NULL OUTPUT AS SELECT @teddy=' (.)_(.) { . . } \_*_/ / . . \ {_} {_} | | (_}- -{_} ' GO --which we can then display with.... DECLARE @teddy VARCHAR(8000) EXECUTE spTeddy @teddy=@teddy OUTPUT SELECT @teddy /* 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. You may think... */ DECLARE @teddy VARCHAR(8000) EXECUTE spTeddy @teddy=@teddy OUTPUT SELECT Reverse(@teddy) GO /* will do the trick. Well almost, but the results need tidying up So we introduce a helping function that reverses some of the characters*/ CREATE FUNCTION dbo.ufsReplaceChars ( @String VARCHAR(8000),--the string within which you want the characters substituted @change VARCHAR(200), --the characters @ReplaceWith VARCHAR(200)--the substitutes ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @ModifiedString VARCHAR(8000) IF Len(@ReplaceWith)<Len(@change) SELECT @change=@change+Space(Len(@change)-Len(@ReplaceWith)) IF (Coalesce(@String,'') ='' ) SELECT @ModifiedString=@String ELSE WHILE Len(@String)>0 IF CharIndex (Substring (@String,1,1), @change) = 0 BEGIN SELECT @ModifiedString = Coalesce(@ModifiedString,'') +Substring (@String,1,1) SELECT @String=Substring (@String,2,Len(@String)-1) END ELSE BEGIN SELECT @ModifiedString = Coalesce(@ModifiedString,'') +Substring (@ReplaceWith,CharIndex (Substring (@String,1,1), @change),1) SELECT @String=Substring (@String,2,Len(@String)-1) END RETURN (@ModifiedString) END DECLARE @teddy VARCHAR(8000) EXECUTE spTeddy @teddy=@teddy OUTPUT SELECT Replace(dbo.ufsReplaceChars(Reverse(@teddy),'_)({}.','¯()}{''') ,Char(10)+Char(13),' ') /* {¯}- -{¯) | | {¯} {¯} \ ' ' / /¯*¯\ { ' ' } (')¯(') All very satisfying. ASCII 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. You'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. Then I got to thinking that I hadn't actually seen a good string compression function around for SQL server ASCII 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 */ character freq. ---------- ----------- ' ' 111 char(10) 9 char(13) 9 '_' 7 '.' 6 '}' 5 '{' 4 '(' 3 ')' 2 '/' 2 '\' 2 '|' 2 '-' 2 '*' 1 /* we can determine this using another helper function that looks at the character count */ GO CREATE FUNCTION dbo.uftvCharacterFrequency /* produces a character count of all the characters in a string as a frequency table*/ ( @String VARCHAR(8000) ) RETURNS @Results TABLE ( [character] VARCHAR(10), [freq.] INT ) AS BEGIN DECLARE @ii INT DECLARE @iiMax INT DECLARE @temp TABLE (Thechar CHAR(1)) SELECT @ii=1, @iiMax=Len( @String) WHILE @ii<=@iiMax BEGIN INSERT INTO @temp SELECT Substring( @String,@ii,1) SELECT @ii=@ii+1 END INSERT INTO @Results SELECT [character]= CASE WHEN Ascii(Thechar)>=32 THEN ''''+Thechar+'''' ELSE 'char('+Convert(VARCHAR(3),Ascii(Thechar))+')' END, [freq.]= Count(*) FROM @temp GROUP BY Thechar ORDER BY Count(*) DESC RETURN END /* we can then see what the character frequency of Teddy is with the following code */ DECLARE @teddy VARCHAR(8000) EXECUTE spTeddy @teddy=@teddy OUTPUT SELECT uftvCharacterFrequency.character, uftvCharacterFrequency.[freq.] FROM dbo.uftvCharacterFrequency(@teddy) /* So The competition here is to provide the best compression/Decompression algorithm for reducing the storage requirements for typical ASCII art such as teddy. This doesn't have to be complex. Even replacing every group of five spaces with a special character reduces Teddy's storage by 38.8% */ |
Load comments