Oracle Illustrated

Feb 21 2010   9:17AM GMT

SPLIT ONE COLUMN WITH DELIMITER TO MULTIPLE COLUMNS

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

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.

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ciera
    Hello, Export the data to a flat file and import it using SQL Assistant's Import Data feature. Use fast export to dump the column from the table to a flat file then reload it using MultiLoad.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: