extract coma separated string into rows
hi team,
i have a column which has values separated by a coma,
I need to extract these values and display them as rows . Any help is really appreciated.
select regexp_substr('cricket,foo
from dual
connect by regexp_instr('cricket,foot
I tried it , it did not work. Is there any other mechanism using which I can extract the data as below
cricket
football
hockey
i have a column which has values separated by a coma,
I need to extract these values and display them as rows . Any help is really appreciated.
select regexp_substr('cricket,foo
from dual
connect by regexp_instr('cricket,foot
I tried it , it did not work. Is there any other mechanism using which I can extract the data as below
cricket
football
hockey
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
select
regexp_substr(mystring,
'[^,]+',
1,
column_value)
mystring
from mydata, table(select collect(level)
from dual
connect by level <= length(mystring) - length(replace(mystring, ',')) + 1)
how you are able to refer to mystring with referring to its table name
regexp_substr(mystring,
'[^,]+',
1,
column_value)
mystring
from mydata, table(select collect(level)
from dual
connect by level <= length(mystring) - length(replace(mystring, ',')) + 1)
how you are able to refer to mystring with referring to its table name
>>how you are able to refer to mystring with referring to its table name
My example uses WITH:
with mydata as (
select 'a,b,X,d' mystring from dual
)
select ...
If you have an actual table and column: remobe the WITH clause on my example leaving only the select.
Replace mydata with your table name and mystring with your column name. Leave everything else as-is.
>>Iam not able to understand the collect level clause
It is pretty much the same connect by level you had. I just used the length functions to determine how many commas a row has.
>>and where condition
What where condition?
My example uses WITH:
with mydata as (
select 'a,b,X,d' mystring from dual
)
select ...
If you have an actual table and column: remobe the WITH clause on my example leaving only the select.
Replace mydata with your table name and mystring with your column name. Leave everything else as-is.
>>Iam not able to understand the collect level clause
It is pretty much the same connect by level you had. I just used the length functions to determine how many commas a row has.
>>and where condition
What where condition?
Try searching for str2tbl
Create a function like that and all of these type queries become much easier. Plus, once you have it you don't need to debug it every time.
Create a function like that and all of these type queries become much easier. Plus, once you have it you don't need to debug it every time.
ASKER