Separate commas from the table column

Question

Hi I have a table column in Oracle 10 which contain a string array like that:

TERMS_TABLE

DOC_ID | TERMS_ARRAY
120 | apple,orange,banana,.....,termN

There are N terms. I want to separate commas from the TERMS_ARRAY and insert them another table

COMMA_SEP_TABLE

DOC_ID |TERM
120 | apple
120 | orange
120 | banana
.....

I try these code but it does not do anything :

 CREATE OR REPLACE SEPERATE_COMMA     IS
  l_tab DBMS_UTILITY.LNAME_ARRAY ;

 l_tablen number;
 CURSOR CUR1 IS SELECT * FROM TERMS_TABLE ;

 BEGIN
  EXECUTE IMMEDIATE ('TRUNCATE TABLE COMMA_SEP_TABLE);

  FOR R IN CUR1
  LOOP
  DBMS_UTILITY.comma_to_table (R.TERMS_ARRAY, l_tablen, l_tab);

  FOR i IN 1 .. l_tablen
  LOOP
     INSERT INTO COMMA_SEP_TABLE
          VALUES (R.DOC_ID, l_tab (i));

     COMMIT;
  END LOOP;
 END LOOP;
 END;

How can separate commas ?

how about this ? : http://stackoverflow.com/questions/1089508/how-to-best-split-csv-strings-in-oracle-9i — Kevin Burton, Oct 26 '11 at 11:14
Go up to the "Search" box at the top-right of the page, enter 'oracle separate comma', and press Enter. This is a common question which has been asked and answered many times. — Bob Jarvis - Слава Україні, Oct 26 '11 at 16:54

score 1 · Accepted Answer · answered Nov 22 '11 at 13:49

1

.....Here is the answer if you are using Oracle 10g and above:

SELECT doc_id,REGEXP_SUBSTR(terms_table.terms_array,'[^,]+',1,LEVEL) term 
FROM terms_table  
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(terms_table.terms_array,'[^,]+')) + 1

answered Nov 22 '11 at 13:49

Kevin Burton

11,676
2
24
37

Separate commas from the table column

1 Answers1