The mouth is the trickiest part. To produce it, we're going to use the help of Pythagoras and his theorem, which states that for any right triangle, the square of the hypotenuse is equal to the sum of the squares of the other two sides. Take another look at Figure 1, and think of the mouth as half a circle with a center located directly between the nostrils. The circle's radius is easy to calculate: num_rows ÷ 2. Note that the code performs an integer division here as well; however, you need an accurate value for the radius that would accommodate a division by 2 (one digit to the right of the decimal point). You can multiply an integer expression by 1. (read "one dot") to force the result to be of a decimal data type instead of an integer. I also subtracted 1 from the number of rows in the table to make the output look more aesthetic.
In T-SQL terms, you express the radius as (1. * COUNT(*) - 1) ÷ 2; its value is calculated in the derived table R to make it available to all the rows in the query. The tricky part here is to calculate, for each row, the distance between the rectangle encapsulating the nose and the beginning of the string that produces the mouth. After calculating that value, you add spaces equal to that distance and concatenate nonspace characters to it to form the mouth.
Looking at a sample row in the right-angled triangle in Figure 1, the missing part of the calculation is side a. The hypotenuse (c) is known—it's equal to the radius. Side b is fairly easy to calculate—ABS(radius - key_col + 1). The Pythagorean theorem says that in a right-angled triangle, the square of the hypotenuse (c) is equal to the sum of the squares of the other sides (a and b). Expressed as an equation, the theorem is a2 + b2 = c2. You have b and c, so you can express the value of a as (check)(c2 b2). In T-SQL, the expression is SQRT(SQUARE(radius)-SQUARE(ABS(radius - key_col + 1))). Finally, wrap the above expression with the SPACE() function to generate the space that precedes the mouth, and concatenate it to the expression you have so far. When you run the concatenated code, you get a face with eyes, nose, and the space that precedes the mouth.
All that's left is to produce the string that makes the mouth itself. The string can contain anything, of course, but I used words to generate the strings. As I mentioned earlier, I stored the binary representation of the words in the #Words table to make the code a little more mysterious and interesting for my students (and readers). To decipher the "sophisticated" encryption algorithm I used, you can use CAST(word AS VARCHAR(15)). Concatenate this CAST expression to what you have so far, and you're almost finished. Thus far, we have a face with eyes, nose, and a mouth that looks more like a beard because each word is of a different size. To make the mouth look like a mouth, you can make all the strings composing the mouth the same size by concatenating a few characters such as a tilde (~) to each word. Use max_word_length - word_length to determine the number of characters that need to be concatenated. The finished query, which Listing 1 shows, produces the result that Figure 2 shows: a smiley face. :-)
Wake Up to the Possibilities
To generate the INSERT statements for this example by using binary values instead of plain text, you can use the script that Listing 4 shows (run it in text mode in Query Analyzer, not in grid mode). You can simply replace the message text in Listing 4 with words from your own message, then run the code in the listing. As I mentioned earlier, the query is dynamic, so the number of rows is unimportant. From my experience, people respond positively when they get a smiley face and a message as a result of some strange-looking T-SQL script. I hope this example keeps you awake for a while; I'll cover more of T-SQL's drawing capabilities in my next column.
End of Article
Prev. page
1
[2]
next page -->
