0

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?

dang
  • 2,342
  • 5
  • 44
  • 91

2 Answers2

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
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • 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. – dang Mar 09 '19 at 14:27
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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Would it also separate multiple values in a single cell to multiple rows? – dang Mar 09 '19 at 14:15
  • The second row has multi line value present. a=old letter ba=older letter xq=newer letter are in 1 cell with new line – dang Mar 09 '19 at 14:16