SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Insert delimited values to a table

752456Oct 19 2010 — edited Oct 19 2010

Dear All,

table name - Test

columns - id, value

User will give the values through procedure.
store_date('USER','12345,ABC,345789,XXX,53898,UUUU'.....)
User may give up to 60 set of
values through the string with ',' delimited value.

I need to store 12345 to id and ABC to value,
345789 to id and XXX to value and so on.

Please guide me to achieve the same.

Bala

Edited by: user6642575 on Oct 19, 2010 2:43 AM

BluShadow

Like this?

SQL> ed
Wrote file afiedt.buf

  1  with t as (select '12345,ABC,345789,XXX,53898,UUUU' as txt from dual)
  2  --
  3  -- END OF TEST DATA
  4  --
  5  select regexp_substr(txt,'[^,]+',1,((rownum-1)*2)+1) as col1
  6        ,regexp_substr(txt,'[^,]+',1,((rownum-1)*2)+2) as col2
  7  from t
  8* connect by rownum <= (length(regexp_replace(txt,'[^,]'))+1)/2
SQL> /

COL1                            COL2
------------------------------- -------------------------------
12345                           ABC
345789                          XXX
53898                           UUUU

SQL>

752456

Thanks for your help.

I need to store the values into the table.

Aketi Jyuuzou

I like recursive with clause B-)

col extStr1 for a10
col extStr2 for a10

with t as (
select '12345,ABC,345789,XXX,53898,UUUU' as txt from dual),
rec(txt,LV,extStr1,extStr2) as(
select txt,1,
RegExp_Substr(txt,'([^,]+),([^,]+)',1,1,null,1),
RegExp_Substr(txt,'([^,]+),([^,]+)',1,1,null,2)
  from t
union all
select txt,LV+1,
RegExp_Substr(txt,'([^,]+),([^,]+)',1,LV+1,null,1),
RegExp_Substr(txt,'([^,]+),([^,]+)',1,LV+1,null,2)
  from rec
 where RegExp_Count(txt,'([^,]+),([^,]+)') >= LV+1)
select extStr1,extStr2
  from rec;

extStr1  extStr2
-------  -------
12345    ABC
345789   XXX
53898    UUUU

Frank Kulash

Hi,

user6642575 wrote:
I need to store the values into the table.

So use an INSERT statement:

INSERT INTO test (id, value)
select regexp_substr(txt,'[^,]+',1,((rownum-1)*2)+1) as id
      ,regexp_substr(txt,'[^,]+',1,((rownum-1)*2)+2) as value
 from t
 connect by rownum <= (length(regexp_replace(txt,'[^,]'))+1)/2

Blushadow's solution assumes the comma-delimited string is in a VARCHAR2 column called txt in a table called t.
If it's actually in a VARCHAR2 variable called txt, not in any table at all, then use dual instead of t:

INSERT INTO test (id, value)
select regexp_substr(txt,'[^,]+',1,((rownum-1)*2)+1) as id
      ,regexp_substr(txt,'[^,]+',1,((rownum-1)*2)+2) as value
 from dual
 connect by rownum <= (length(regexp_replace(txt,'[^,]'))+1)/2

752456

Thanks all for your instant help.

1 - 5

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 16 2010

Added on Oct 19 2010

#pl-sql

5 comments

485 views

SQL & PL/SQL

Insert delimited values to a table

Comments

Post Details