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?
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;
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;
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
