(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:

  /* Let's have a config-subquery so we can
     easily change the delimiter if we want */
  config as (
      ',' as delimiter
    from dual
  /* This is our input, a comma separated list of wookies */
  input as (
      '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
      -- 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
        nullif(instr(text, config.delimiter),0), -- NULL if delimiter not found
      ) 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
    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
        prev.end_pos+1 as start_pos,
          nullif(instr(text, config.delimiter, prev.end_pos+1),0), -- NULL if delimiter not found
        ) as end_pos,
    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;

Have fun!


Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.