Search notes:

Oracle SQL: CSV lines to columns

This example tries to demonstrate how regexp_replace can be used to transform CSV text that is stored in a single column in a table into multiple columns in a select statement.

First, we need a table for our test data …

create table tq84_csv_data (
   csv_line   varchar2(100)
);

Github repository Oracle-Patterns, path: /SQL/select/misc/csv_to_columns/create-table.sql

… and fill it with CSV like data:

insert into tq84_csv_data values ( 'one,two,three,four');
insert into tq84_csv_data values ( 'foo,bar,baz,qux'   );
insert into tq84_csv_data values ( '1,,3,'             );
insert into tq84_csv_data values ( 'i,ii,iii,iv'       );
insert into tq84_csv_data values ( ' ,**,***,****'     );
insert into tq84_csv_data values ( ',,,'               );

Github repository Oracle-Patterns, path: /SQL/select/misc/csv_to_columns/insert-values.sql

The following select statement creates a column for each value in our CSV data. It uses the regular expression pattern [^,]* which means: any count of non-comma characters.

For the first column, we require that the returned value starts at the beginning of our CSV data, hence the ^ in front of the patter.

The columns 2, 3 and 4 use the occurrence parameter to return the respective value.

select
   trim(',' from regexp_substr(csv_line, '^[^,]*' ,1, 1)) col_1,
   trim(',' from regexp_substr(csv_line, ',[^,]*' ,1, 1)) col_2,
   trim(',' from regexp_substr(csv_line, ',[^,]*' ,1, 2)) col_3,
   trim(',' from regexp_substr(csv_line, ',[^,]*' ,1, 3)) col_4
from
   tq84_csv_data;
--
-- COL_1      COL_2      COL_3      COL_4
-- ---------- ---------- ---------- ----------
-- one        two        three      four
-- foo        bar        baz        qux
-- 1                     3
-- i          ii         iii        iv
--            **         ***        ****

Github repository Oracle-Patterns, path: /SQL/select/misc/csv_to_columns/select.sql

Cleaning up:

drop table tq84_csv_data purge;

Github repository Oracle-Patterns, path: /SQL/select/misc/csv_to_columns/drop-table.sql

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1740430862, '13.59.182.74', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/SQL/select/misc/csv_to_columns(78): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78