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.