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
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:
<< Home
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.
However, I think a new challenge is in order: Set SSMS to auto-scroll mode and figure out how to ANIMATE this image.
inventory journal
operation software
director software
driver software
factory software
pub software
baker software
baker software
county software
dancing log
Post a Comment
operation software
director software
driver software
factory software
pub software
baker software
baker software
county software
dancing log
<< Home