Split multiple values in a cell to multiple rows - Oracle SQL

Question

I have a table:

table1

values
------------
x=new letter
------------
a=old letter
ba=older letter
xq=newer letter
------------
xf=new apple
xt=new orange
x3=new fruit
xtt=new seed

I have to separate the values in each cell to multiple rows.

The following is the output:

table2

code      description
x         new letter
a         old letter
ba        older letter
xq        newer letter
xf        new apple
xt        new orange
x3        new fruit
xtt       new seed

How can this be achieved?

look at this link : stackoverflow.com/questions/28677070/… — saravanatn
– saravanatn, Commented Mar 8, 2019 at 9:56

Gordon Linoff · Accepted Answer · 2019-03-08 12:22:52Z

2

I would use regexp_replace() or regexp_substr():

select regexp_substr(str, '^[^=]+') as code,
       regexp_substr(str, '[^=]+$') as value

Here is a db<>fiddle.

Note that this does not use values for the column name. That is a very bad choice for a column name because it is a SQL keyword.

answered Mar 8, 2019 at 12:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dang Over a year ago

Would it also separate multiple values in a single cell to multiple rows?

dang Over a year ago

The second row has multi line value present. a=old letter ba=older letter xq=newer letter are in 1 cell with new line

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-08 10:01:17Z

2

try like below

SELECT NVL(SUBSTR('a=old letter', 0, INSTR('a=old letter', '=')-1), 'a=old letter') 
AS col1, NVL(SUBSTR('a=old letter', INSTR('a=old letter', '=')+1), 'a=old letter')    
  FROM DUAL

so in you case

SELECT NVL(SUBSTR(values, 0, INSTR(values, '=')-1), values) 
AS col1, NVL(SUBSTR(values, INSTR(values, '=')+1), values) 

  FROM table1

answered Mar 8, 2019 at 10:01

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

1 Comment

dang Over a year ago

the second cell contains multi line values - a=old letter ba=older letter xq=newer letter, the sql above does not break multi line value to separate rows.

Collectives™ on Stack Overflow

Split multiple values in a cell to multiple rows - Oracle SQL

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related