CODEWITHSUNDEEP
sqloracle-databaseselect
jijo
jijo

Reputation: 815

SQL query to fetch rows based on a column

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. enter image description here

Upvotes: 0

Views: 106

Answers (2)

Sanjay Radadiya
Sanjay Radadiya

Reputation: 1286

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;

Upvotes: 1

Daniel Langemann
Daniel Langemann

Reputation: 540

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

Upvotes: 2

Related Questions