3 ms·
Having learned SQL on a need-to-know basis, recursion took me a while to grok. Would have been nice to have this article back then. Quite powerful stuff, both
by magicalhippo 5y ago
Having learned SQL on a need-to-know basis, recursion took me a while to grok. Would have been nice to have this article back then.
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.
- magicalhippo 5y agoIn case anyone is curious, here's how I solved the CSV split thing. Again, my SQL skills are limited, but it did the job for me. 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