(Image by Andrew Martin from Pixabay – modifications by Sam)
In a previous post I showed how we can split a String into several rows with pure SQL – but I made a couple of mistakes, so it wouldn’t work correctly in several circumstances.
So I want to share a quick, revisited version of my solution, which also removes a bit of code duplication we had before:
with /* Let's have a config-subquery so we can easily change the delimiter if we want */ config as ( select ',' as delimiter from dual ), /* This is our input, a comma separated list of wookies */ input as ( select 'Chewbacca,Chewnucca,Orlyn,Babba' as text from dual ), /* Here is where the magic happens: a recursive query */ element_positions(start_pos, end_pos, element_no, text) as ( /* We start with the first line, which also is our staring point and anchor */ select -- Start position inside the text is always 1 1 as start_pos, -- End position: first occurence of delimiter or if no delimiter -- exists, the length of the whole string coalesce( nullif(instr(text, config.delimiter),0), -- NULL if delimiter not found length(text)+1 ) as end_pos, -- Line-Number: obvious 1 as element_no, -- We pass the text along so we don't need to -- select it every time from the DDL query text from input, config /* Now the recursion starts, defining all further lines. What we do here is pretty much the same as before, it's just more text, because we need to give some additional boundaries, like starting point for INSTR search */ union all select prev.end_pos+1 as start_pos, coalesce( nullif(instr(text, config.delimiter, prev.end_pos+1),0), -- NULL if delimiter not found length(text)+1 ) as end_pos, prev.element_no+1, prev.text from element_positions prev, config -- We add lines as long as there have been delimiters where instr(prev.text, config.delimiter, prev.start_pos) > 0 ), /* In order to avoid code duplication, we do the final slicing in a separate subquery */ elements as ( select ep.*, substr(text, start_pos, end_pos-start_pos) element from element_positions ep ) select element_no, element from elements;