Split string oracle into a single column and insert into a table

Question

I have a table with column data in below format(Row# just to indicate row number).

Row#1 :test.doc#delimiter#1234,test1.doc#delimiter#1235,test2.doc#delimiter#1236<br>
Row#2 :fil1.txt#delimiter#1456,fil1.txt#delimiter#1457

I want to split the string using comma(,) as delimiter, list all in one single column and insert into a new table.

output should be something like this (Row# just to indicate row number):

Row#1:test.doc#delimiter#1234<br>
Row#2:test1.doc#delimiter#1235<br>
Row#3:test2.doc#delimiter#1236<br>
Row#4: fil1.txt#delimiter#1456

Can anyone help me do this?

do you want to find each existing delimiter and split in this locations ? — Hard Worker
– Hard Worker, Commented Apr 16, 2015 at 18:53
possible duplicate of Oracle 10g Splitting string into multiple rows — MT0
– MT0, Commented Apr 16, 2015 at 20:12

MT0 · Accepted Answer · 2015-04-16 20:18:11Z

2

WITH data AS (
  SELECT 'test.doc#delimiter#1234,test1.doc#delimiter#1235,test2.doc#delimiter#1236' AS "value" FROM DUAL
  UNION ALL
  SELECT 'fil1.txt#delimiter#1456,fil1.txt#delimiter#1457' AS "value" FROM DUAL
)
SELECT  REGEXP_SUBSTR( data."value", '[^,]+', 1, levels.COLUMN_VALUE )
FROM    data,
        TABLE(
          CAST(
            MULTISET(
              SELECT LEVEL
              FROM   DUAL
              CONNECT BY  LEVEL <= LENGTH( regexp_replace( "value", '[^,]+'))  + 1
            ) AS sys.OdciNumberList
          )
        ) levels;

answered Apr 16, 2015 at 20:18

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MT0 Over a year ago

@Aramillo The OP states: (Row# just to indicate row number)

ruudvan · Accepted Answer · 2015-04-16 20:11:29Z

1

An easy way would be to, for each row, split the string into multiple rows and insert it to the destination table. There are multiple ways to split a single string into multiple rows on this site. A simple search should give you some answers.

Assuming your table is called origtable, the column that has those strings is called mycol and destination table is called destTable.

BEGIN
   FOR orig_row IN (SELECT mycol from origTable) LOOP
      INSERT INTO destTable(outCol)
      SELECT REGEXP_SUBSTR (orig_row.mycol,'(.*?)(,|$)',1,LEVEL,NULL,1)
            FROM dual
      CONNECT BY LEVEL <= REGEXP_COUNT (orig_row.mycol, ',') + 1;
   END LOOP;
END;

answered Apr 16, 2015 at 20:11

ruudvan

1,3816 silver badges16 bronze badges

Comments

Hard Worker · Accepted Answer · 2015-04-16 18:58:46Z

0

if you want to find an existing delimier and split it

try this query :

select  substr(filed_name ,
               REGEXP_SUBSTR(filed_name,'#demilimer#',1),
               (REGEXP_SUBSTR(filed_name,'#demilimer#',2)-1)
    ) col1  ,  

  substr(filed_name ,
               REGEXP_SUBSTR(filed_name,'#demilimer#',2),
               REGEXP_SUBSTR(filed_name,'#demilimer#',3)- (REGEXP_SUBSTR(filed_name,'#demilimer#',2))
    ) col2  ,
     substr(filed_name ,
               REGEXP_SUBSTR(filed_name,'#demilimer#',3),
               length(filed_name)- (REGEXP_SUBSTR(filed_name,'#demilimer#',3))
    ) col3        /* last col should take up to the length  */

from table_name

answered Apr 16, 2015 at 18:58

Hard Worker

1,1711 gold badge18 silver badges45 bronze badges

Collectives™ on Stack Overflow

Split string oracle into a single column and insert into a table

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related