Split a String into Rows by Delimiter (with Pure SQL)

(Image by Andrew Martin from Pixabay – modifications by Sam)

When you work with SQL, you usually want to have data that is nicely arranged into columns and rows. That’s where SQL shines and where it can feel like Magic.

However, sometimes we have data that is not split into columns and rows, for example when we deal with some legacy code input or call dbms_metadata.get_ddl on a table.

(Yes, we can select most of the information dbms_metadata provides by querying the data dictionary, but there are some edge cases where we can’t, for example whether an identity column is GENERATED ALWAYS or GENERATED BY DEFAULT)

Thanks to recursive queries, we can solve nearly every programming problem in pure SQL, so let’s implement a split into rows by delimiter query.

Our input is the result of dbms_metadata.get_ddl for a table that holds information about Wookies:

create table wookies
(
  id int generated always as identity,
  name varchar2(200),
  height number(5,2)
);

select dbms_metadata.get_ddl('TABLE', 'WOOKIES')
from dual;

/* Output:

  CREATE TABLE "SITHDB"."WOOKIES" 
   (	"ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE, 
	"NAME" VARCHAR2(200), 
	"HEIGHT" NUMBER(5,2)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS NOLOGGING
  TABLESPACE "USERS" 
*/

Wouldn’t it be great if we could get that DDL output in separate lines, so we can easily query for different columns?

with
  /* Let's have a config-subquery so we can
     easily change the delimiter if we want */
  config as (
    select
      chr(10) as delimiter
    from dual
  ),
  /* This is our input, the DDL of the WOOKIES table
     Of course, this could be any other input */
  ddl as (
    select
      dbms_metadata.get_ddl('TABLE', 'WOOKIES') as text
    from dual
  ),
  /* Here is where the magic happens: a recursive query
   */
  lines(start_pos, end_pos, line, line_no, text) as (
    /* We start with the first line, which also
       is our starting point and anchor for the recursion
     */
    select
      -- Start position inside the text is always 1
      1 as start_pos,
      -- End position: first occurence of delimiter
      instr(text, config.delimiter) as end_pos,
      -- Line: all text between start and end position
      substr(text, 1, instr(text, config.delimiter)-1) as line,
      -- Line-Number: obvious
      1 as line_no,
      -- We pass the text along so we don't need to
      -- select it every time from the DDL query
      text
    from ddl, config
    /* Now the recursion starts, defining all
       fruther 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,
        instr(prev.text, config.delimiter, prev.end_pos+1) as end_pos,
        substr(prev.text, prev.end_pos+1, instr(prev.text, config.delimiter, prev.end_pos+1)-1) as line,
        prev.line_no+1,
        prev.text
    from lines prev, config
    -- We add lines as long as there are more delimiters
    where instr(prev.text, config.delimiter, prev.end_pos+1) > 0
  )
select line_no, line from lines;

Of course, you can do this with any text and any delimiter (if you have multi-char delimiters, you will need to replace the +1/-1 with +length(delimiter) etc.)

You can try it out on Oracle LiveSQL and find the whole example as always on my repository.