One column in my table stores list of values separated by comma (I know it's a poor design, but refactoring is not an option at the current moment). I seem to come up with a working solution but I'm afraid it's not very efficient :
select distinct test_id , regexp_substr(str_data,'[^,]+', 1, level) from ( select 1 as test_id, '1,2,3' as str_data from dual union all select 2 as test_id, '4,5,6' as str_data from dual )test_data connect by regexp_substr(str_data, '[^,]+', 1, level) is not null
I don't like that I have to use
distinct . Is there a better approach ?