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:
