Skip to content
Advertisement

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

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?

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement