I have a table Employee as below. The Change column contains names of columns whose values are modified through my application. Data in this column is separated by comma. I need to query this table in such a way that the result will have one change per row. ie split the data in the Change column by comma and fetch the corresponding row. I don't have an idea where to start ! Please help.
Add a comment
|
2 Answers
Let's see, you could use Oracle's regexp_substr function:
select distinct Id, Name, Address, trim(regexp_substr(Change,'[^,]+', 1, level))
from Employee
connect by regexp_substr(Change, '[^,]+', 1, level) is not null;
This should work for any number of comma-separated values in your Change column.
See doc on the rexexp_substr function here: https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm
Comments
here i have tried with the use of regexp_substr included multiset level
with temp as
(
select id, name, address, change from testemp
)
select id,name,address,trim(regexp_substr(change, '[^,]+', 1, levels.column_value)) change
from temp t,
table(cast(multiset(select level from dual
connect by level <= length (regexp_replace(change, '[^,]+')) + 1)
as sys.OdciNumberList)) levels;
Comments
