Christmas SQL

December 20, 2021

1–2 minutes

Just a re-post of something I put on StackOverflow last year so I can refer back to it easily. What better way to celebrate Christmas than with SQL Smile


select replace(replace(replace(r,'X',chr(27)||'[42m'||chr(27)||'[1;'||to_char(32)||'m'||'X'||chr(27)||'[0m'),
    'T',chr(27)||'[43m'||chr(27)||'[1;'||to_char(33)||'m'||'T'||chr(27)||'[0m'),
    '@',chr(27)||'[33m'||chr(27)||'[1;'||to_char(31)||'m'||'@'||chr(27)||'[0m')
from ( select lpad(' ',20-e-i)|| case when dbms_random.value < 0.3 then substr(s,1,e*2-3+i*2) 
       else substr(substr(s,1,dbms_random.value(1,e*2-3+i*2-1))||'@'||s,1,e*2-3+i*2) end r
from ( select rpad('X',40,'X') s,rpad('T',40,'T') t from dual ) , 
( select level i, level+2 hop from dual connect by level <= 4 ) , lateral
( select level e from dual connect by level <= hop ) union all select lpad(' ',17)||substr(t,1,3)
from ( select rpad('X',40,'X') s,rpad('T',40,'T') t from dual ) connect by level <= 5 );

Each execution gets a different distribution of christmas lights Smile . Enjoy!

enter image description here

4 responses to “Christmas SQL”

Rajeshwaran Jeyabal

December 20, 2021 at 12:18 pm

Happy Holidays and Merry Christmas to you Connor.

Here is another version of Chrismas tree to you from SQL. Hope you love it.

demo@XEPDB1> select decode
2 (sign (floor (maxwidth/2) – rownum)
3 ,1, lpad (‘ ‘, floor (maxwidth/2) – (rownum -1))
4 ||rpad (‘*’, 2 * (rownum -1), ‘ *’)
5 ,lpad (‘* * *’, floor (maxwidth/2) + 3)) “Merry Christmas”
6 from dual
7 , (select 40 as maxwidth from dual)
8 connect by level < floor (maxwidth/2) + 5
9 /

*
* *
* * *
* * * *
* * * * *
* * * * * *
* * * * * * *
* * * * * * * *
* * * * * * * * *
* * * * * * * * * *
* * * * * * * * * * *
* * * * * * * * * * * *
* * * * * * * * * * * * *
* * * * * * * * * * * * * *
* * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * *
* * *
* * *
* * *
* * *
* * *

24 rows selected.

Reply
Iudith Mentzel

December 21, 2021 at 1:44 am

Hi Connor,
I would just like to wish you and your whole family Merry Christmas and Happy New Year 2022 🙂
And a huge thank you for everything you are doing for the Oracle community 🙂
Still hope that day will come and we will be able to meet again.

Cheers & Best Regards,
Iudith Mentzel

Reply
1. Connor McDonald
  
  December 21, 2021 at 8:25 am
  
  And back to you as well Iudith. Hopefully Israel will one day come calling again!
  Merry Christmas!
  
  Reply
Lynne Flatley

December 21, 2021 at 11:44 pm

Sweet! Merry Christmas to you and yours, Connor!

Reply