My coworker Darren came up with something.
The other day I tried to understand his lateral example but couldn't quite
grasp it. Now I think it works like this:
You calculate a column on the fly and 'toss a lateral' which appends this
calculated column on the end of your currently selected row.
The beauty of this that you don't have to keep recalculating the position
of the first comma when getting to each subsequent comma.
This is working quite nicely:
cast(substr(hmcoutput,1,c1-1) as char(10)) as LparName,
cast(substr(hmcoutput,c1+1,c2-c1-1) as dec(3,0)) as LparNumber,
cast(substr(hmcoutput,c2+1) as char(10)) as LparStatus
from rack2hst.hmcoutput t
, lateral(values locate(',',t.hmcoutput)) L1 (c1)
, lateral(values locate(',',t.hmcoutput,L1.c1+1)) L2 (c2)
May be slightly confusing because it's an internally defined table in
which the column name, record format and table name are all the same.
'toss a lateral' Yep, it's only 3.5 hours until gates open for the first
Friday Night Lights of the season. "Only an intersquad scrimmage!?" Don't
say that to the almost full stands desperate for some local HS football