Separate string csv in oracle

Question

Have a csv string with values "A1;B2;C3" and need get the values for separate and then insert the values in a table how separate this string? Edit: i Use this

SELECT TRIM(SUBSTR(txt ,  INSTR (txt, ';', 1, level ) + 1, 
                          INSTR (txt, ';', 1, level+1) - INSTR (txt, ';', 1, level) -1 
                              ) 
                        )
                    AS TOKEN
            FROM ( SELECT ';'||'8;9;7;6'||';' AS txt  FROM dual )
            CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,';',''))-1

and get the values for separate but, need asign this values in variables

[This shows you how to split a value into rows](http://stackoverflow.com/a/17802176/266304), in 11g anyway; not sure if the question quote counts as a duplicate. — Alex Poole, Apr 30 '14 at 14:19
only need get the values and put in varibles for then insert into a table :( — , Apr 30 '14 at 14:30
Why do you need to put them into variables before inserting? Doesn't `insert into select ...` work here? — Alex Poole, Apr 30 '14 at 14:41
but the string maybe get more values or less not is static, maybe with a loop work, but i don't know how aplicate in this case — , Apr 30 '14 at 14:46

score 0 · Accepted Answer · answered Apr 30 '14 at 14:29

0

it is not clear if you want to insert them into 1 record or create a set.

if you want to insert into individual fields, you would use SUBSTR and INSTR functions like this:

with s as (select ';' || 'aa;bb;cc' || ';' str from dual)
select 
substr(s.str,instr(s.str, ';',1,1)+1, instr(s.str, ';',1,2)-instr(s.str, ';',1,1)-1), 
substr(s.str,instr(s.str, ';',1,2)+1, instr(s.str, ';',1,3)-instr(s.str, ';',1,2)-1), 
substr(s.str,instr(s.str, ';',1,3)+1, instr(s.str, ';',1,4)-instr(s.str, ';',1,3)-1)
from s

If you want to create a set

answered Apr 30 '14 at 14:29

vav

4,584
2
19
39

This work but, need add SUBSRT for any value, maybe the string get one o more values...the form with i think, is with a loop, record the string and assign the values to variables and then insert into a table. – Apr 30 '14 at 14:40
if it is a record, you know how many variables you may have. If it is a set, go with another aproach from the link. – vav Apr 30 '14 at 14:50
mmm my bad, yep i know how many records get in the string, in this case how insert this in a table directly – Apr 30 '14 at 15:04

Separate string csv in oracle

1 Answers1