Like Day-5, I accepted a slight efficiency hit by reading the data twice for logical convenience. I read the numbers as one CTE and then read the corresponding operators as a second CTE.
Since the data is irregularly spaced I used regular expression to collapse multiple spaces into a single space as a delimiter between values. Then using STR2TBL I split each row into a collection of numbers. Each value is numbered 1-n from left to right. I do similar space collapsing and numbering of the operators.
Once I have the numbers and operators sequenced, it’s just a matter of joining them on their corresponding sequenced “RN” values. If the operator is + then I SUM the results. If the operator is * then I multiply the results. I use the classic EXP(SUM(LN(x))) to create a multiplication aggregate. The nested functions introduce some insignificant (10-26) floating point noise, but ROUND cleans them up, returning the results back as an integer.
Summing each of the sub-results gives the final answer.
WITH
numbers
AS
(SELECT rn, n
FROM (SELECT TO_NUMBER(n) n, ROW_NUMBER() OVER(PARTITION BY seq ORDER BY rown) rn
FROM (SELECT d.*, s.COLUMN_VALUE n, ROWNUM rown
FROM advent.data2rows('advent2025-6') d
CROSS APPLY str2tbl(REGEXP_REPLACE(TRIM(d.str), ' +', ' '), ' ') s
WHERE REGEXP_LIKE(str, '[0-9]+')) x)),
opers
AS
(SELECT rn, oper
FROM (SELECT x.*, ROW_NUMBER() OVER(PARTITION BY seq ORDER BY rown) rn
FROM (SELECT d.*, s.COLUMN_VALUE oper, ROWNUM rown
FROM advent.data2rows('advent2025-6') d
CROSS APPLY str2tbl(REGEXP_REPLACE(TRIM(d.str), ' +', ' '), ' ') s
WHERE REGEXP_LIKE(str, '[*+]')) x))
SELECT SUM(val)
FROM ( SELECT n.rn, CASE WHEN MAX(o.oper) = '+' THEN SUM(n.n) ELSE (EXP(SUM(LN(n.n)))) END val
FROM numbers n JOIN opers o ON n.rn = o.rn
GROUP BY n.rn);
For part 2, the problem is the same as far as the math goes, but parsing out the values is a little odd. I used my data2charmap function to parse the data into a collection of X,Y coordinates for each character. Keeping track of the operator position as the start (S) of each set of columns gave me something consistent to group on. I pivoted the digits into new numbers by grouping on the X position and ordering by Y for each starting position S. Since the operator is always going to be in the first X position I had to split it off from the digits with RTRIM and CASE.
At this point I have the vertical numbers assembled along with the corresponding operator for each group and the next steps follow the same as in Part 1. Returning the SUM for + and the product for * for each S-group and then summing those sub-results to get the final answer.
SELECT SUM(result)
FROM ( SELECT s, CASE WHEN MAX(oper) = '*' THEN ROUND(EXP(SUM(LN(n)))) ELSE SUM(n) END result
FROM ( SELECT s,
x,
TO_NUMBER(RTRIM(LISTAGG(c) WITHIN GROUP (ORDER BY y), ' *+')) n,
MAX(CASE WHEN c IN ('*', '+') THEN c END) oper
FROM (SELECT c,
x,
y,
NVL(
LAST_VALUE(CASE WHEN c IN ('*', '+') THEN x END IGNORE NULLS)
OVER(ORDER BY x, y DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1) s
FROM advent.data2charmap('advent2025-6') s)
GROUP BY s, x)
GROUP BY s);
Part 2 intimidated me when I first read it, thinking it would be a monster parsing the irregularly arranged data; but once I sat down to actually try it, the pivot seemed fairly obvious and using the data2charmap function provided an convenient way to get the coordinates needed to do the pivoting.
The Advent of Code is short this year at only 12 puzzles, but I’m still playing catch up so I’ll see how many more I can get done before I break for the holidays.