0

I have the input

var1,var2,var3

and I need to insert or update into 3 row table with split by ",", How do I do that, thank you

JimmyN
  • 579
  • 4
  • 16
  • 1
    You should go here: [Splitting string into multiple rows in Oracle](http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle). Please search before asking. – Pham X. Bach Apr 21 '16 at 04:43

1 Answers1

2

If i understood your question correctly you want to create three rows out of one input text line broken down by some delimitter. You can do this with Regex_substr

CREATE TABLE TEST AS ( 
SELECT REGEXP_SUBSTR ('var1,var2,var3','[^,]+',1 ,LEVEL) values 
FROM DUAL 
CONNECT BY REGEXP_SUBSTR ('var1,var2,var3','[^,]+' ,1 ,LEVEL) IS NOT NULL)

and just the insert as follow up:

INSERT INTO TEST (
SELECT REGEXP_SUBSTR ('var1,var2,var3', '[^,]+',1,LEVEL) VALUES
FROM DUAL
CONNECT BY REGEXP_SUBSTR ('var1,var2,var3','[^,]+' ,1 ,LEVEL) IS NOT NULL)

For the update you need to be more specific on what the operation (before -> after) shall accomplish

Markus Zaczek
  • 154
  • 6