Quite powerful stuff, both in the results it can produce and in the resources it can drain from the server.
Besides the typical "linked-list" queries, I used this to split a comma-separated column value into separate rows, as our DB server did not have that as a built-in function. Not pretty, but it did the job.
with recursive lst (subidx, elm, input_str) as (
( -- initial row
select nullif(charindex(',', input_str), 0) as subidx, coalesce(substr(input_str, 1, subidx-1), input_str) as elm, input_col as input_str
from (select 'abc, d, ef, ghj' as input_col) x -- replace subquery with something useful
)
union all
( -- recursive
select nullif(charindex(',', substr(input_str, subidx+1)), 0)+subidx as next_subidx, substr(input_str, subidx+1, coalesce(next_subidx-1-subidx, length(input_str))) as next_elm, input_str as next_input_str
from lst
where subidx < length(input_str)
)
)
select trim(elm) from lst