SPLIT ONE COLUMN WITH DELIMITER TO MULTIPLE COLUMNS

If a delimiter is available on a column and if that needs to be split to multiple columns, then we usually use INSTR and SUBSTR to split into multiple columns and insert into table. Following is a simple demonstration without using multiple INSTR's and SUBSTR's and insert one column with a specified delimiter into multiple columns.

Step 1 : Create table script

drop table test_tab

create table test_tab
(col1 varchar2(20),
col2 varchar2(20),
col3 varchar2(20),
col4 varchar2(20),
col5 varchar2(20),
col6 varchar2(20),
col7 varchar2(20),
col8 varchar2(20),
col9 varchar2(20),
col10 varchar2(20));

Step 2. PL/SQL script

set serveroutput on size 100000
declare
v_data VARCHAR2(200);
begin
SELECT 'INSERT INTO TEST_TAB VALUES ('
||''''
||replace(qry.dat, '$', ''',''')
|| REPLACE(RPAD(chr(10),LENGTH2, chr(14)),chr(14), ''',''')
||''''
||')'
INTO v_data
from
(SELECT
10 - (length(a.dat) - length(translate( a.dat, chr(0)||'$', chr(0)))) LENGTH2, a.dat
FROM
(select 'abcd$efgh$hijk%lmn$uvp' dat from dual) a) qry;

dbms_output.put_line('Value of v_data is' || v_data);

EXECUTE IMMEDIATE v_Data;
END;

SQL> @col_tocols.sql
21 /
Value of v_data isINSERT INTO TEST_TAB VALUES
('abcd','efgh','hijk%lmn','uvp
','','','','','','')

PL/SQL procedure successfully completed.

OUTPUT -

SQL> select * from test_Tab;

COL1 COL2 COL3
-------------------- -------------------- --------------------
COL4 COL5 COL6
-------------------- -------------------- --------------------
COL7 COL8 COL9
-------------------- -------------------- --------------------
COL10
--------------------
abcd efgh hijk%lmn
uvp


Notes

Line 1 - SELECT 'INSERT INTO TEST_TAB VALUES ('
Line 2 - ||''''
Line 3 - ||replace(qry.dat, '$', ''',''')
Line 4 - || REPLACE(RPAD(chr(10),LENGTH2, chr(14)),chr(14), ''',''')
Line 5 - ||''''
Line 6 - ||')'
Line 7 - INTO v_data
Line 8 - from
Line 9 - (SELECT
Line 10 - 10 - (length(a.dat) - length(translate( a.dat, chr(0)||'$', chr(0)))) Line 11 - LENGTH2, a.dat
Line 12 - FROM
Line 13 - (select 'abcd$efgh$hijk%lmn$uvp' dat from dual) a) qry;

1. Line 3 - replace(qry.dat, '$', ''',''') -> Search character $ would be replaced with ','.

2. Line 4 - REPLACE(RPAD(chr(10),LENGTH2, chr(14)),chr(14), ''',''') -> In the above example there are 3 "$" symbols - these will be replaced with ','. For 3 search strings "$" this will be split to 3 columns only. But, in the table there are 10 columns. So, to fill rest of the columns use RPAD function.

3. Line 10 & 11 - Translate function will replace one to one character. chr(0) is NULL. chr(0)||'$' would be trranslated to chr(0) ignoring the '$' which would give the LENGTH of the string excluding the search character.

Now, formula is -
10 MINUS (Total length of the string MINUS Length of the string excluding search character) -> This will give the length of left out columns in the table.

10 - (22 - 19) => 7.