CODEWITHSUNDEEP
sqloracle-databaseoracle10goracle-sqldeveloper
roshanK
roshanK

Reputation: 409

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

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?

Upvotes: 1

Views: 3107

Answers (3)

MT0
MT0

Reputation: 168232

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;

Upvotes: 2

ruudvan
ruudvan

Reputation: 1371

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;

Upvotes: 1

Hard Worker
Hard Worker

Reputation: 1121

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

Upvotes: 0

Related Questions