Oracle Live SQL - Script: SQL queries to split CSV or other delimited strings into rows

Statement 1

This uses the connect by level trick to create a row for each value in the string. For row N, the regular expression extract the value at position N.

Split a CSV to rows with SQL

with rws as (  
  select 'split,into,rows' str from dual  
)  
  select regexp_substr (  
           str,  
           '[^,]+',  
           1,  
           level  
         ) value  
  from   rws  
  connect by level <=   
    length ( str ) - length ( replace ( str, ',' ) ) + 1


VALUE
split
into
rows

3 rows selected.

VALUE
split
into
rows

Statement 2

If the input string can have leading or trailing commas, trim these before calculating how many values there are.

Split a CSV with leading and trailing commas

with rws as (  
  select ',leading,commas,and,trailing,' str from dual  
)  
  select regexp_substr (  
           str,  
           '[^,]+',  
           1,  
           level  
         ) value  
  from   rws  
  connect by level <=   
    length ( trim ( both ',' from str ) ) - length ( replace ( str, ',' ) ) + 1


VALUE
leading
commas
and
trailing

4 rows selected.

VALUE
leading
commas
and
trailing

Statement 3

The method works for any delimiters; this splits a semicolon separated string.

Split semicolon separated values

with rws as (  
  select 'split;semicolons;into;rows' str from dual  
)  
  select regexp_substr (  
           str,  
           '[^;]+',  
           1,  
           level  
         ) value  
  from   rws  
  connect by level <=   
    length ( str ) - length ( replace ( str, ';' ) ) + 1


VALUE
split
semicolons
into
rows

4 rows selected.

VALUE
split
semicolons
into
rows

Statement 4

To use in, each value you're searching for must be a separate string.

IN list queries

select employee_id, first_name, last_name  
from   hr.employees  
where  last_name in ( 'King', 'Kochhar', 'De Haan' )


EMPLOYEE_ID FIRST_NAME LAST_NAME
102 Lex De Haan
156 Janette King
100 Steven King
101 Neena Kochhar

4 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME
102	Lex	De Haan
156	Janette	King
100	Steven	King
101	Neena	Kochhar

Statement 5

This searches for rows where the last name equals "King,Kochhar,De Haan". You have to split the string into separate values to find the matching names.

Must pass separate values to IN

select employee_id, first_name, last_name  
from   hr.employees  
where  last_name in ( 'King,Kochhar,De Haan' )

no data found

Statement 6

By turning the CSV into rows, you can pass the search string as a single value.

Using CSV-to-rows to do a variable IN list

with rws as (  
  select 'King,Kochhar,De Haan' str from dual  
)  
select employee_id, first_name, last_name  
from   hr.employees  
where  last_name in (   
  select regexp_substr (  
           str,  
           '[^,]+',  
           1,  
           level  
         ) value  
  from   rws  
  connect by level <=   
    length ( str ) - length ( replace ( str, ',' ) ) + 1  
)


EMPLOYEE_ID FIRST_NAME LAST_NAME
156 Janette King
100 Steven King
102 Lex De Haan
101 Neena Kochhar

4 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME
156	Janette	King
100	Steven	King
102	Lex	De Haan
101	Neena	Kochhar

Statement 7

This enables you to use a bind variable for the CSV search string. So you can do a variable in list with a single string.

declare  
  emp_cur     sys_refcursor;  
  search_str  varchar2(100) := 'King,Kochhar,De Haan';  
  employee_id integer;  
  first_name  varchar2(30);  
  last_name   varchar2(30);  
begin  
  open emp_cur for q'!select employee_id, first_name, last_name  
from   hr.employees  
where  last_name in (   
  select regexp_substr (  
           :str,  
           '[^,]+',  
           1,  
           level  
         ) value  
  from   dual  
  connect by level <=   
    length ( :str ) - length ( replace ( :str, ',' ) ) + 1  
)!' using search_str, search_str, search_str;  
  
  loop  
    fetch emp_cur into employee_id, first_name, last_name;  
    exit when emp_cur%notfound;  
    dbms_output.put_line (    
      employee_id || ' ' || first_name || ' ' || last_name   
    );  
  end loop;  
end;

Statement processed.
156 Janette King
100 Steven King
102 Lex De Haan
101 Neena Kochhar

Statement 8

Create a table storing CSVs

create table csvs (  
  csv_id   integer  
    primary key,  
  csv_text varchar2(1000)  
)

Table created.

Statement 9

insert into csvs  
  values ( 1, 'split,into,rows' )

1 row(s) inserted.

Statement 10

insert into csvs  
  values ( 2, 'even,more,values,to,extract' )

1 row(s) inserted.

Statement 11

commit

Statement processed.

Statement 12

To use the connect by method to split delimited values stored in a table, use a lateral join as the row generator.

Split CSVs in columns

select csv_id, regexp_substr (  
         csv_text,  
         '[^,]+',  
         1,  
         rn  
       ) val  
from   csvs  
cross  join lateral (  
  select level rn from dual  
  connect by level <=     
    length ( csv_text ) - length ( replace ( csv_text, ',' ) ) + 1  
)


CSV_ID VAL
1 split
1 into
1 rows
2 even
2 more
2 values
2 to
2 extract

8 rows selected.

CSV_ID	VAL
1	split
1	into
1	rows
2	even
2	more
2	values
2	to
2	extract

Statement 13

The lateral join was added in 12c. If you need to split delimited values in a table using earlier releases of Oracle Database you can use this method instead.

Split CSVs in columns method 2

select csv_id, regexp_substr (  
         csv_text,  
         '[^,]+',  
         1,  
         level  
       ) val  
from   csvs   
connect by level <=     
  length ( csv_text ) - length ( replace ( csv_text, ',' ) ) + 1  
and    prior csv_text = csv_text  
and    prior sys_guid () is not null


CSV_ID VAL
2 even
2 more
2 values
2 to
2 extract
1 split
1 into
1 rows

8 rows selected.

CSV_ID	VAL
2	even
2	more
2	values
2	to
2	extract
1	split
1	into
1	rows

Statement 14

This splits comma-separated values that themselves contain commas! To ensure this works properly, the values must be enclosed in quotes.

Splitting CSVs containing commas and enclosed in quotes

with rws as (  
  select department_id, listagg ( '"' || last_name || ', ' || first_name || '"', ',' )   
           within group ( order by employee_id ) str  
  from   hr.employees  
  where  department_id in ( 10, 20, 30 )  
  group  by department_id  
)  
  select department_id, regexp_substr (  
           str,  
           '[^"]+',  
           instr  ( str, '"', 1, (rn*2)-1 ),   
           1  
         ) employee
  from   rws, lateral (  
    select level rn from dual  
    connect by level <=     
      regexp_count ( str, '","' ) + 1  
  )


DEPARTMENT_ID EMPLOYEE INSTR(STR,'"',1,(RN*2)-1)
10 Whalen, Jennifer 1
20 Hartstein, Michael 1
20 Fay, Pat 22
30 Raphaely, Den 1
30 Khoo, Alexander 17
30 Baida, Shelli 35
30 Tobias, Sigal 51
30 Himuro, Guy 67
30 Colmenares, Karen 81

9 rows selected.

DEPARTMENT_ID	EMPLOYEE	INSTR(STR,'"',1,(RN*2)-1)
10	Whalen, Jennifer	1
20	Hartstein, Michael	1
20	Fay, Pat	22
30	Raphaely, Den	1
30	Khoo, Alexander	17
30	Baida, Shelli	35
30	Tobias, Sigal	51
30	Himuro, Guy	67
30	Colmenares, Karen	81

Statement 15

To make a pipelined table function, you need to create a nested table for its return type.

Nested table of strings

create or replace type string_table as  
  table of varchar2 (4000);

Type created.

Statement 16

This iterates through the delimited values in the input, piping each out one.

Pipelined table function to split rows

create or replace function split_string ( 
  delimited_string varchar2, 
  separator        varchar2 default ',' 
) return string_table 
  pipelined 
as 
  delimited_string_cleaned varchar2(32767); 
  substring varchar2(4000); 
  pos       pls_integer; 
begin 
  delimited_string_cleaned := trim ( both separator from delimited_string ) || separator; 
  pos := instr ( delimited_string_cleaned, separator ); 
  substring := substr ( delimited_string_cleaned, 1, pos - 1 ); 
   
  loop 
    exit when substring is null; 
    pipe row ( substring ); 
   
    substring := substr (  
      delimited_string_cleaned,  
      pos + 1,  
      instr ( delimited_string_cleaned, separator, pos + 1 ) - pos - 1  
    ); 
    pos := instr ( delimited_string_cleaned, separator, pos + 1 );    
  end loop; 
 
  return; 
end;

Function created.

Statement 17

Calling the pipelined table function

select * 
from   split_string ( 'King,Kochhar,De Haan' )


COLUMN_VALUE
King
Kochhar
De Haan

3 rows selected.

COLUMN_VALUE
King
Kochhar
De Haan

Statement 18

This creates an overloaded table SQL macro to split CSV. One accepts a single string, the other a table and column containing CSVs.

CSV-to-rows SQL macro function

create or replace package string_macros_pkg as   
  function split_string (   
    tab dbms_tf.table_t,  
    col dbms_tf.columns_t,  
    separator varchar2 default ','  
  ) return clob sql_macro;  
    
  function split_string (   
    string varchar2,  
    separator varchar2 default ','  
  ) return clob sql_macro;  
end;

Package created.

Statement 19

SQL macros are essentially query templates. At parse time the database resolves these, swapping out the parameters in the return string for the actual text of the parameters passed.

CSV-to-rows SQL macro function bodies

create or replace package body string_macros_pkg as   
  function split_string (   
    tab dbms_tf.table_t,  
    col dbms_tf.columns_t,  
    separator varchar2 default ','  
  ) return clob sql_macro as  
    sql_text clob;  
  begin  
      
    sql_text := 'select t.*,   
         regexp_substr (  
           ' || col ( 1 ) || ',  
           ''[^'' || separator || '']+'',  
           1,  
           pos  
         ) str,  
         pos  
  from   tab t,  
         lateral (  
           select level pos  
           from   dual  
           connect by level <=   
             length ( ' || col ( 1 ) || ' )   
               - length ( replace ( ' || col ( 1 ) || ', separator ) ) + 1  
         )';  
    
    return sql_text;  
      
  end split_string;  
    
  function split_string (   
    string varchar2,  
    separator varchar2 default ','  
  ) return clob sql_macro as  
      sql_text clob;  
  begin  
      
    sql_text := 'select   
         regexp_substr (  
           string,  
           ''[^'' || separator || '']+'',  
           1,  
           level  
         ) str,  
         level pos  
  from   dual  
  connect by level <=   
    length ( string )   
      - length ( replace ( string, separator ) ) + 1';  
    
    return sql_text;  
      
  end split_string;  
  
end;

Package Body created.

Statement 20

This calls the SQL macro using a single CSV string as a variable IN list

SQL macro variable IN list

select employee_id, first_name, last_name  
from   hr.employees  
where  last_name in (  
  select str   
  from   string_macros_pkg.split_string ( 'King,Kochhar,De Haan' )  
)


EMPLOYEE_ID FIRST_NAME LAST_NAME
100 Steven King
156 Janette King
101 Neena Kochhar
102 Lex De Haan

4 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME
100	Steven	King
156	Janette	King
101	Neena	Kochhar
102	Lex	De Haan

Statement 21

A table SQL macro works like a normal table - you can join its output to other tables.

Join to a table SQL macro

select employee_id, first_name, last_name   
from   hr.employees  
join   string_macros_pkg.split_string ( 'King,Kochhar,De Haan' )  
on     last_name = str


EMPLOYEE_ID FIRST_NAME LAST_NAME
156 Janette King
100 Steven King
101 Neena Kochhar
102 Lex De Haan

4 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME
156	Janette	King
100	Steven	King
101	Neena	Kochhar
102	Lex	De Haan

Statement 22

This calls the table overload of the macro, passing the table CSVS and its column CSV_TEXT

Calling a SQL macro to turn saved strings into rows

select csv_id, str, pos   
from   string_macros_pkg.split_string (   
  csvs, columns ( csv_text ), ','   
)


CSV_ID STR POS
1 split 1
1 into 2
1 rows 3
2 even 1
2 more 2
2 values 3
2 to 4
2 extract 5

8 rows selected.

SQL queries to split CSV or other delimited strings into rows

Additional Information