jijo jijo - 4 months ago 6
SQL Question

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

Answer

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