Ha: regexp_split_to_table
Date: Wed, 23 Jan 2013 11:33:12 +0400
Message-ID: <OF11CB28EC.BCD86C44-ON44257AFC.002563F3-44257AFC.0029A086_at_psbank.ru>
Hi,
there are many well-known variants.
Some of them for 11gR2:
create table po as
select 1 id, '10,11,12,13' val from dual
union all
select 2 id, '200,201' val from dual
union all
select 3 id, '300' val from dual
union all
select 4 id, null val from dual
;
-- xml ora:tokenize function:
select id, n
from po
,xmltable('ora:tokenize(.,",")' passing val||',' columns n int path '.'
)
where n is not null;
- join with pivot table + regexp_substr/substr or other select id ,to_number(regexp_substr(val,'\d+',1,gen.i)) n from po ,(select level i from dual connect by level<=4000) gen where gen.i<=regexp_count(val,'\d+') order by 1,2;
- recursive subquery factoring: with t(id,val,pos_start,pos_end) as ( select id ,val ,1 ,instr(val,',',1) from po union all select id ,val ,pos_end+1 ,instr(val,',',pos_end+1) from t where pos_end>0 ) select t.id ,substr(val,pos_start,decode(pos_end,0,4000,pos_end-pos_start)) n from t where val is not null order by 1,2
Best regards,
Sayan Malakshinov
http://orasql.org
|------------>
| οΤ: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |jo <jose.soares_at_sferacarta.com> | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| λΟΝΥ: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |ORACLE-L <oracle-l_at_freelists.org> | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| δΑΤΑ: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |23.01.2013 10:42 | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| τΕΝΑ: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |regexp_split_to_table | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| οΤ: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |oracle-l-bounce_at_freelists.org | >--------------------------------------------------------------------------------------------------------------------------------------------------|
Hi all,
I need to find in Oracle a function similar to Postgres
regexp_split_to_table function
to split values from a text column into a list of numeric values as in:
select * from po;
id | val
----+----------------------
1 | 1,2,3,4,5,6,7,8,9,10
(1 row)
select cast(regexp_split_to_table(val, ',') as int) as numbers from po;
numbers
1
2
3
4
5
6
7
8
9
10
(10 rows)
Is there a simple way to do this thing in Oracle?
Thanks for any help.
j
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 23 2013 - 08:33:12 CET