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.