# 100CodeExamples - Split a String into Rows by Delimiter

• Script Name 100CodeExamples - Split a String into Rows by Delimiter
• Visibility Unlisted - anyone with the share link can access
• Description https://developer-sam.de/2021/03/split-a-string-into-rows-by-delimiter-with-pure-sql/
• Area SQL General / SQL Query
• Contributor Pesse
• Created Friday March 12, 2021
• Statement 1
``````create table wookies
(
id int generated always as identity,
name varchar2(200),
height number(5,2)
)``````
Table created.
• Statement 2
``````select dbms_metadata.get_ddl('TABLE', 'WOOKIES')
from dual``````
CREATE TABLE "SQL_FZFXRKZOZXQMAQLDQOJFUUMMA"."WOOKIES" ( "ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, "NAME" VARCHAR2(200) COLLATE "USING_NLS_COMP", "HEIGHT" NUMBER(5,2) ) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "LIVESQL_USERS"
• Statement 3
``````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
from dual
),
/* Here is where the magic happens: a recursive query
*/
lines(start_pos, end_pos, line, line_no, text) as (
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
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``````
LINE_NOLINE
1 -
2 CREATE TABLE "SQL_FZFXRKZOZXQMAQLDQOJFUUMMA"."WOOKIES"
3 ( "ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, "NAME" VARCHAR2(200) COLLATE "USING_NLS_COMP", "HEIGHT"
4 "NAME" VARCHAR2(200) COLLATE "USING_NLS_COMP", "HEIGHT" NUMBER(5,2) ) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "LIVESQL_USERS"
5 "HEIGHT" NUMBER(5,2) ) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "LIVESQL_USERS"
6 ) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "LIVESQL_USERS"
7 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "LIVESQL_USERS"
8 NOCOMPRESS LOGGING TABLESPACE "LIVESQL_USERS"

8 rows selected.