jamtronix: more silly sql

I was amused by Phil Factor's gems of twisted SQL, like his code for compressing ASCII teddy bears, or The Sudoku Puzzle Generator,

So here's my contribution, a function that makes 'banners' suitable for printing on a dot matrix printer. (all code in this post was prettified with Simple-Talk SQL Prettifier )

First, we need a table that contains some font data, e.g.

SELECT * FROM font_data WHERE CHAR_number=ASCII('A') ORDER BY row_number




char_number,row_number,row_data 
65,0,   ###   
65,1,  ## ##  
65,2, ##   ## 
65,3, ##   ## 
65,4, ####### 
65,5, ##   ## 
65,6, ##   ## 
65,7,         

(8 row(s) affected)

One way to get this table is to dump the standard 8x25 text mode font from the BIOS ROM, which is stored at memory location FFA6:000E. We can call the MS-DOS debug.com command to get this as some hex digits, and then unpack them into 8 rows of 8 chars each, like this:

------------- SQL STARTS HERE ---------

SET nocount ON

CREATE TABLE #bits_for_hex_digit(hex_digit CHAR(1),bits CHAR(4))
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('0','    ')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('1','   #')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('2','  # ')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('3','  ##')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('4',' #  ')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('5',' # #')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('6',' ## ')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('7',' ###')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('8','#   ')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('9','#  #')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('A','# # ')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('B','# ##')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('C','##  ')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('D','## #')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('E','### ')
INSERT INTO #bits_for_hex_digit(hex_digit,bits) VALUES ('F','####')

CREATE TABLE #rom_font_dump(data VARCHAR(100))

CREATE TABLE font_data(CHAR_number INT, row_number INT,row_data CHAR(8))

DECLARE @temp_file VARCHAR(100)
DECLARE @cmd VARCHAR(100)
SET @temp_file='%TMP%\debug.txt'

SET @cmd='echo d FFA6:000E l 400 >'+@temp_file
EXEC xp_cmdshell @cmd , no_output
SET @cmd='echo q >>'+@temp_file
EXEC xp_cmdshell @cmd , no_output
SET @cmd='debug <'+@temp_file
INSERT INTO #rom_font_dump(data) EXEC xp_cmdshell @cmd

DECLARE @byte_counter INT
SET @byte_counter=0

DECLARE row_cursor CURSOR fast_forward FOR
SELECT data FROM #rom_font_dump
       WHERE data LIKE 'ffa6:%'
       ORDER BY data

DECLARE @data VARCHAR(100)
OPEN row_cursor
FETCH next FROM row_cursor INTO @data
WHILE @@fetch_status >-1 BEGIN
       DECLARE @i INT
       SET @i=12
       WHILE @i<63 BEGIN
               DECLARE @row CHAR(8)
               SET @row=NULL
                SELECT @row=d1.bits+d2.bits FROM #bits_for_hex_digit d1
                       INNER JOIN #bits_for_hex_digit d2
                       ON d1.hex_digit=SUBSTRING(@data,@i,1)
                       AND d2.hex_digit=SUBSTRING(@data,@i+1,1)
               IF @row IS NOT NULL BEGIN
                       INSERT INTO font_data(CHAR_number, row_number,row_data)
                               SELECT @byte_counter/8, @byte_counter%8, @row
                       SET @byte_counter=@byte_counter+1
               END
               SET @i=@i+3

       END

       FETCH next FROM row_cursor INTO @data
END
CLOSE row_cursor
DEALLOCATE row_cursor

DROP TABLE #bits_for_hex_digit
DROP TABLE #rom_font_dump

------------- SQL ENDS HERE ---------

This is the function that uses that table to turn a string into a banner:

------------- SQL STARTS HERE ---------

CREATE function ufn_Banner(@s VARCHAR(100))
returns VARCHAR(8000) AS
BEGIN

       DECLARE @row_0 VARCHAR(1000)
       DECLARE @row_1 VARCHAR(1000)
       DECLARE @row_2 VARCHAR(1000)
       DECLARE @row_3 VARCHAR(1000)
       DECLARE @row_4 VARCHAR(1000)
       DECLARE @row_5 VARCHAR(1000)
       DECLARE @row_6 VARCHAR(1000)
       DECLARE @row_7 VARCHAR(1000)

       SET @row_0 =''
       SET @row_1 =''
       SET @row_2 =''
       SET @row_3 =''
       SET @row_4 =''
       SET @row_5 =''
       SET @row_6 =''
       SET @row_7 =''

       DECLARE @i INT
       SET @i=0
       WHILE @i<=LEN(@s)
       BEGIN
               DECLARE @c CHAR(1)
               SET @c=SUBSTRING(@s,@i,1)
                       SELECT @row_0=@row_0+row_data FROM font_data WHERE CHAR_number=ASCII(@c) AND row_number=0
                       SELECT @row_1=@row_1+row_data FROM font_data WHERE CHAR_number=ASCII(@c) AND row_number=1
                       SELECT @row_2=@row_2+row_data FROM font_data WHERE CHAR_number=ASCII(@c) AND row_number=2
                       SELECT @row_3=@row_3+row_data FROM font_data WHERE CHAR_number=ASCII(@c) AND row_number=3
                       SELECT @row_4=@row_4+row_data FROM font_data WHERE CHAR_number=ASCII(@c) AND row_number=4
                       SELECT @row_5=@row_5+row_data FROM font_data WHERE CHAR_number=ASCII(@c) AND row_number=5
                       SELECT @row_6=@row_6+row_data FROM font_data WHERE CHAR_number=ASCII(@c) AND row_number=6
                       SELECT @row_7=@row_7+row_data FROM font_data WHERE CHAR_number=ASCII(@c) AND row_number=7
               SET @i=@i+1

       END
       RETURN
               @row_0+CHAR(10)
               +@row_1+CHAR(10)
               +@row_2+CHAR(10)
               +@row_3+CHAR(10)
               +@row_4+CHAR(10)
               +@row_5+CHAR(10)
               +@row_6+CHAR(10)
               +@row_7+CHAR(10)
       END

------------- SQL ENDS HERE ---------

And finally, here's some example screenshots.

First, showing the output in SQL Server Management Studio

You could also pop up a banner in internet explorer, using usp_Show_HTML, like this:

jamtronix

The older I get, the better I was

more silly sql