A couple of years back I posted a simple SQL to draw a Christmas tree, complete with colours and randomly placed lights using the Windows terminal colour codes to. My SQL ended up like this:
with codes as (select '[1;' s1, '[0m' e1 , rpad('X',40,'X') s, rpad('T',40,'T') t from dual)
select replace(
replace(
replace(r,'X',chr(27)||'[42m'||chr(27)||s1||to_char(32)||'m'||'X'||chr(27)||e1),
'T',chr(27)||'[43m'||chr(27)||s1||to_char(33)||'m'||'T'||chr(27)||e1),
'@',chr(27)||'[33m'||chr(27)||s1||to_char(31)||'m'||'@'||chr(27)||e1)
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, s1, e1
from codes,
(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), s1, e1
from codes
connect by level <= 5
);
and we can see that each execution gets a nice tree with some random light placements.
So fast forward to 2025 and asked ChatGPT to give me a SQL to do the same. It took a few prompts to get me to a result that was nearly what I wanted, and we ended up with the following SQL
WITH esc AS (
SELECT CHR(27) || '[' AS e FROM dual
),
base AS (
SELECT 1 AS rn, ' * ' AS rowtxt FROM dual UNION ALL
SELECT 2, ' *** ' FROM dual UNION ALL
SELECT 3, ' ***** ' FROM dual UNION ALL
SELECT 4, ' ******* ' FROM dual UNION ALL
SELECT 5, ' ********* ' FROM dual UNION ALL
SELECT 6, ' *********** ' FROM dual UNION ALL
SELECT 7, ' ************* ' FROM dual UNION ALL
SELECT 8, ' *************** ' FROM dual UNION ALL
SELECT 9, ' ### ' FROM dual UNION ALL
SELECT 10, ' ### ' FROM dual
),
decorated AS (
SELECT
rn,
-- Add ornaments and flashing colors inside foliage:
CASE
WHEN rn = 1 THEN REGEXP_REPLACE(rowtxt, '\*', CHR(42)) -- star later colored
WHEN rn BETWEEN 2 AND 8 THEN
REGEXP_REPLACE(
rowtxt,
'\*',
CASE
WHEN MOD(ABS(DBMS_RANDOM.RANDOM), 8) = 0 THEN 'o' -- ornament
WHEN MOD(ABS(DBMS_RANDOM.RANDOM), 5) = 0 THEN '+' -- flashing light
ELSE '*' -- normal leaf
END
)
ELSE rowtxt
END AS rowtxt
FROM base
),
colored AS (
SELECT
rn,
CASE
WHEN rn = 1 THEN (SELECT e||'33;1m' FROM esc) -- bright gold star
WHEN rn BETWEEN 2 AND 3 THEN (SELECT e||'32;1m' FROM esc) -- bright green
WHEN rn BETWEEN 4 AND 5 THEN (SELECT e||'32m' FROM esc) -- normal green
WHEN rn BETWEEN 6 AND 8 THEN (SELECT e||'32;2m' FROM esc) -- dim green (gradient)
ELSE (SELECT e||'90m' FROM esc) -- grey trunk
END AS prefix_color,
--
-- Inner decoration coloring (ornaments, lights)
REGEXP_REPLACE(
rowtxt,
'[o+*]',
CASE
WHEN REGEXP_SUBSTR(rowtxt, '[o+*]', 1, 1) = 'o'
THEN (SELECT e||'31;1m' FROM esc) || 'o' || (SELECT e||'0m' FROM esc)
WHEN REGEXP_SUBSTR(rowtxt, '[o+*]', 1, 1) = '+'
THEN (SELECT e||'36;1m' FROM esc) || '+' || (SELECT e||'0m' FROM esc)
ELSE (SELECT e||'32m' FROM esc) || '*' || (SELECT e||'0m' FROM esc)
END,
1,
0,
'm'
) AS colored_row
FROM decorated
)
SELECT prefix_color || colored_row || (SELECT e||'0m' FROM esc)
FROM colored
ORDER BY rn;
which gives me a colour tree, with multi-coloured….rows?
Anyway, it still did pretty well I think, but which it does show one quirk I’ve observed most of the time I’ve asked an AI to give me some SQL. That SQL is typically very close to correct (which is very cool) but often it is a very verbose implementation of the requirement. That’s not necessarily a bad thing – you can see that ChatGPT did a better job of commenting its code than I did 🙂 But always see if you can determine a simpler version of what an LLM gives you. Simplicity = easier maintenance.
Note: “Simplicity” is also not the same as “Shorter length”
Leave a Reply