FOR NUMBERS
with mytable as
(
select 'smith' ename, '1,2,3,4,5' mytext from dual
union
select 'ward' ename, '2,4,6,7' mytext from dual
)
select ename,(column_value).getnumberval()
from mytable, xmltable(mytext)
OUTPUT
FOR STRINGS USE (column_value).getstringval()
with mytable as
(
select 'smith' ename, '"a","b","c","d","e"' mytext from dual
union
select 'ward' ename, '"b","d","f","g"' mytext from dual
)
select ename,(column_value).getstringval()
from mytable, xmltable(mytext)
Related Links:
- Function to Compare two Comma Separated Strings in Oracle
- Oracle: New String Aggregation Techniques
- Oracle: Difference between wm_concat and ListAgg
- Oracle: Comma Separated String To Rows 10g Onwards
- Oracle: Nth Record from Comma Separated String
- Oracle: Department wise Employee Ids Comma Separated
- Oracle: Comma Separated String To Rows
with mytable as
(
select 'smith' ename, '1,2,3,4,5' mytext from dual
union
select 'ward' ename, '2,4,6,7' mytext from dual
)
select ename,(column_value).getnumberval()
from mytable, xmltable(mytext)
OUTPUT
FOR STRINGS USE (column_value).getstringval()
with mytable as
(
select 'smith' ename, '"a","b","c","d","e"' mytext from dual
union
select 'ward' ename, '"b","d","f","g"' mytext from dual
)
select ename,(column_value).getstringval()
from mytable, xmltable(mytext)
Related Links:
- Function to Compare two Comma Separated Strings in Oracle
- Oracle: New String Aggregation Techniques
- Oracle: Difference between wm_concat and ListAgg
- Oracle: Comma Separated String To Rows 10g Onwards
- Oracle: Nth Record from Comma Separated String
- Oracle: Department wise Employee Ids Comma Separated
- Oracle: Comma Separated String To Rows
doesnt work!!!! at least in 11 g
ReplyDeleteshow me how did you tried
DeleteMany, many thanks for this.
ReplyDeleteYou saved me a lot of time and work.
Many thanks from me, as well. We've been using custom functions and SQL types for this, and it's great to find a simpler method, especially one that uses nothing but Oracle supplied functionality.
ReplyDeleteRuss Whiteman
Great! Thanks so much. Simple and works better and faster than other solution like connect by etc...
ReplyDeleteDoesn't work for me either..... tried like below
ReplyDeletewith mytable as
(
select STATE, city from TABLEA where state ='AP'
)
select STATE,(column_value).getstringval()
from mytable, xmltable(city);
============
ORA-19112: error raised during evaluation:
XVM-01002: [XPDY0002] Dynamic context component 'context item' has no value
19112. 00000 - "error raised during evaluation: %s"
*Cause: The error function was called during evaluation of the XQuery expression.
*Action: Check the detailed error message for the possible causes.
xmltable(city) should contain comma separated value in following format '"a","b","c","d","e"'
DeleteIt worked...thanks a lot...I was using the traditional method of regular exp + connect by...but this seems to be easy, simple and probably faster....thanks a lot !! It helps.
ReplyDeleteWhat if your data only has double quotes in it or say '&' then its giving error
ReplyDeleteORA-19112: error raised during evaluation:
XVM-01002: [XPDY0002] Dynamic context component 'context item' has no value
19112. 00000 - "error raised during evaluation: %s"
*Cause: The error function was called during evaluation of the XQuery expression.
*Action: Check the detailed error message for the possible causes.
is there an alternative?? I tried regexp using connect by but its huge data and taking much time
Super
ReplyDeleteSQL> select * from data1;
ReplyDeletesmith 1,2,3,4
gappu 5,6,7,8
SQL> select distinct name, regexp_substr(data,'[0-9]{1}',1,level) as dt from data1 connect by level<=regexp_count(data,',')+1 order by dt;
smith 1
smith 2
smith 3
smith 4
gappu 5
gappu 6
gappu 7
gappu 8
8 rows selected.
I hope you are doing well as I write this. I appreciate you bringing up "Oracle: Comma Separated String to Rows 11g" with an emphasis on Connectors-1. It can be difficult to work with comma-separated strings in Oracle, and using connectors like Connectors-1 increases both the complexity and efficiency of the process.
ReplyDelete