Friday, August 04, 2006

 

T-SQL Drawing

Here is a humble attempt to draw a landscape using T-SQL !

NOTE: Before running this query, you should set the dispaly mode to text and Results font to a fixed width Font (Mine is Courier new) in Query Analyzer

SET NOCOUNT ON

DECLARE @ TABLE(id int IDENTITY(1,1), pic CHAR(120))

INSERT INTO @
SELECT REPLICATE(CHAR(126),120)
FROM (SELECT 1 AS N UNION ALL SELECT 2) A,
(SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) B,
(SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) C

--Tree 1
UPDATE @
SET pic = STUFF(pic, (50 - (-3+ id)) , ((-3+ id) * 2), REPLICATE(CHAR(47),(-3+ id))+ REPLICATE(CHAR(92),(-3+ id)))
WHERE id BETWEEN 4 AND 26

UPDATE @
SET Pic = STUFF(Pic,49, 2, CHAR(43)+CHAR(43))
WHERE id >=6

UPDATE @
SET Pic = STUFF(Pic,48, 4, REPLICATE(CHAR(124),4))
WHERE id >=26

--Tree 2
UPDATE @
SET Pic = STUFF(Pic, 100 - ((id - 6)*3), 6 + (((id -6) * 6)), REPLICATE(CHAR(37), 6 + (((id -6) * 6))))
WHERE ID BETWEEN 6 AND 10

UPDATE @
SET Pic = STUFF(Pic, 89 + ((id -11)*2), 28 - ((id -11)*4), REPLICATE(CHAR(37), 28 - ((id-11)*4)))
WHERE ID BETWEEN 11 AND 14

UPDATE @ SET Pic = STUFF(Pic, 102,2,CHAR(64)+CHAR(64))
WHERE Id >= 14 AND ID <= 25

--Add a branch
UPDATE @
SET Pic = STUFF(Pic, 90,12,REPLICATE(CHAR(61),12))
WHERE Id = 19

UPDATE @
SET Pic = STUFF(Pic, CASE WHEN id % 2 = 0 THEN 88 ELSE 87 END, 3, REPLICATE(CHAR(37), 3))
WHERE ID IN(18,19,20)

--Add a Bird too :)
UPDATE @
SET Pic = STUFF(Pic, 94, 3 , CONVERT(VARCHAR, 0x2F5E5C))
WHERE ID = 20

UPDATE @
SET Pic = STUFF(Pic, 94, 3 , CHAR(43) + CHAR(32) + CHAR(43))
WHERE ID = 19

UPDATE @
SET Pic = STUFF(Pic, 91, 9 , CONVERT(VARCHAR, 0x205C5C2022202F2F20))
WHERE ID = 18

UPDATE @
SET Pic = STUFF(Pic, 91, 9 , CONVERT(VARCHAR, 0x205F7B2A762A7D5F20))
WHERE ID = 17

UPDATE @
SET Pic = STUFF(Pic, 92, 7 , CONVERT(VARCHAR, 0x2020205F202020))
WHERE ID = 16

--Add a house in the hills
UPDATE @
SET Pic = STUFF(Pic, 66, 12 , CONVERT(VARCHAR, 0x7C5F5F5F5F5F5F7C5F7C5F7C))WHERE ID = 11

UPDATE @
SET Pic = STUFF(Pic, 66, 12 , CONVERT(VARCHAR, 0x7C5B5D205B5D207C207C207C))
WHERE ID = 10

UPDATE @
SET Pic = STUFF(Pic, 66, 12 , CONVERT(VARCHAR, 0x2F2F2F2F2F2F2F2F5F5F5F5C))
WHERE ID = 9

UPDATE @
SET Pic = STUFF(Pic, 67, 11 , CONVERT(VARCHAR, 0x2F2F2F2F2F2F2F2F635C))
WHERE ID = 8

UPDATE @
SET Pic = STUFF(Pic, 68, 8 , CONVERT(VARCHAR, 0x5F5F5F5F5F5F5F50))
WHERE ID = 7

--Add fruits to the first tree in random locations.
UPDATE @
SET Pic = STUFF(Pic, ((50 - (id - 4))+ (CAST(RAND() * (id -4) AS INT))), 2, CHAR(123) + CHAR(125))
WHERE id BETWEEN 10 AND 26

UPDATE @
SET Pic = STUFF(Pic, 50 + CAST(RAND() * (id -4) AS INT), 2, CHAR(123) + CHAR(125))
WHERE id BETWEEN 10 AND 26

UPDATE @
SET Pic = STUFF(Pic, ((50 - (id - 4))+ (CAST(RAND() * (id -4) AS INT))), 2, CHAR(123) + CHAR(125))
WHERE id BETWEEN 10 AND 26

UPDATE @
SET Pic = STUFF(Pic, 50 + CAST(RAND() * (id -4) AS INT), 2, CHAR(123) + CHAR(125))
WHERE id BETWEEN 10 AND 26

--Sun
UPDATE @
SET pic = STUFF(Pic,10,6, REPLICATE(CHAR(32),6))
WHERE id IN(2,5)

UPDATE @ SET pic = STUFF(Pic,9,8, REPLICATE(CHAR(32),8))
WHERE id IN(3,4)

--Add the superCool SQL Guy
UPDATE @ SET Pic = STUFF(Pic, 10,7, CONVERT(VARCHAR, 0x285F5F5B5F5F29))
WHERE ID = 28

UPDATE @
SET Pic = STUFF(Pic, 12,5, CONVERT(VARCHAR, 0x5F2F5F5F2F))
WHERE ID = 27

UPDATE @
SET Pic = STUFF(Pic, 12,6, CONVERT(VARCHAR, 0x207C2F20202F))
WHERE ID = 26

UPDATE @
SET Pic = STUFF(Pic, 12,6, CONVERT(VARCHAR, 0x207C5C20205C))
WHERE ID = 25

UPDATE @
SET Pic = STUFF(Pic, 12,6, CONVERT(VARCHAR, 0x5F5F5F285F2F))
WHERE ID = 24

UPDATE @
SET Pic = STUFF(Pic, 16,3, CONVERT(VARCHAR, 0x2F5F2F))
WHERE ID = 23

UPDATE @
SET Pic = STUFF(Pic, 16,3, CONVERT(VARCHAR, 0x5C205C))
WHERE ID = 22

UPDATE @
SET Pic = STUFF(Pic, 10,8, CONVERT(VARCHAR, 0x2F20205C2F20205C))
WHERE ID = 21

UPDATE @
SET Pic = STUFF(Pic, 11,6, CONVERT(VARCHAR, 0x5F5C206F2F5F))
WHERE ID = 20

UPDATE @
SET Pic = STUFF(Pic, 11,6, CONVERT(VARCHAR, 0x5C287E6F6F20))
WHERE ID = 19

UPDATE @
SET Pic = STUFF(Pic, 11,6, CONVERT(VARCHAR, 0x2F20205F5F29))
WHERE ID = 18

UPDATE @
SET Pic = STUFF(Pic, 12,4, REPLICATE(CHAR(95),4))
WHERE ID = 17

UPDATE @
SET Pic = STUFF(Pic, 9,3,CONVERT(VARCHAR, 0x2F202F))
WHERE ID = 22

UPDATE @
SET Pic = STUFF(Pic, 9+ (id -23),2, CONVERT(VARCHAR, 0x5C20))
WHERE ID IN(23,24)

UPDATE @
SET Pic = STUFF(Pic, 11,1, CHAR(124))
WHERE ID BETWEEN 23 AND 27

UPDATE @
SET Pic = STUFF(Pic, 12,4, CASE WHEN id % 2 = 1 THEN CONVERT(VARCHAR, 0x20475559) ELSE CONVERT(VARCHAR, 0x2053514C) END)
WHERE ID IN(22,23)

--Some Bush
UPDATE @
SET Pic = REPLICATE(CHAR(92)+CHAR(124)+ CHAR(47),40)
WHERE id = 31

--River (for Veer :)
UPDATE @
SET Pic = STUFF(pic, (78 + ((31 - id)*5)), (40 -((31 - id)*5)+2), LEFT(CHAR(63) + REPLICATE(CONVERT(VARCHAR, 0x2D205F207E5E7E2D205F7E207E20),4), (40 -((31 - id)*5)+2) ))
WHERE ID BETWEEN 23 AND 31

--And a couple of Fish too
UPDATE @
SET Pic = STUFF(Pic,CASE WHEN ID < 30 THEN 108 ELSE 95 END , 7, CONVERT(VARCHAR, 0x3E3C2828282A3E))
WHERE ID IN(27,30)

UPDATE @
SET Pic = STUFF(Pic,105 , 5, CONVERT(VARCHAR, 0x3C275F3E3C))
WHERE ID IN(28,31)

--Border
UPDATE @
SET Pic = REPLICATE(CHAR(95),120)
WHERE ID IN(1,32)

UPDATE @
SET Pic = STUFF(STUFF(Pic,1,1,CHAR(124)),120,1,CHAR(124))
WHERE id > 1

--Signature
UPDATE @
SET Pic = STUFF(Pic, 112,6, CONVERT(VARCHAR,0x526F6A697074))
WHERE ID = 31

SELECT Pic [ ] FROM @ ORDER By Id


Comments:
THATS GREAT !!!

mY rIVER AND fISH TOOOOOOOOOOOOOOOOOOOOO AWESOMEEEEEEEEEEEEEEEEEEEEEEE rOJI ...
 
I would like to know the usage of it in real time?
 
Glad to meet you in the blogosphere, although the post sounds like Greek to me. :-) Came to know about your blog through the ENAM newsletter archives.
 
Great job, Roji!

However, I think a new challenge is in order: Set SSMS to auto-scroll mode and figure out how to ANIMATE this image.
 
How did you get those hexa decimal values?
 
nifty one. :) like it
 
superb roji
 
This is a great article, supplies the useful information for me.
 
This sounds awesome; is there a pic of the final product?
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?