with t as (
select 'A:B:C@A:B:C@A:B:C@A:B:C@A:B:C@A:B:C@' str from dual
)
select regexp_substr(str,'[^:@]+',1,column_value * 3 + 1 ) col1,
regexp_substr(str,'[^:@]+',1,column_value * 3 + 2 ) col2,
regexp_substr(str,'[^:@]+',1,column_value * 3 + 3 ) col3
from t,
table(
cast(
multiset(
select level - 1
from dual
connect by level <= length(str) - length(replace(str,'@'))
)
as sys.OdciNumberList
)
)
/
COL1 COL2 COL3
------------------------------------ ------------------------------------ ------
A B C
A B C
A B C
A B C
A B C
A B C
6 rows selected.
SQL>
SY. Solomon Yakobson wrote:Solomon you know any docs tutorial on regular expression with examplesSY.with t as ( select 'A:B:C@A:B:C@A:B:C@A:B:C@A:B:C@A:B:C@' str from dual ) select regexp_substr(str,'[^:@]+',1,column_value * 3 + 1 ) col1, regexp_substr(str,'[^:@]+',1,column_value * 3 + 2 ) col2, regexp_substr(str,'[^:@]+',1,column_value * 3 + 3 ) col3 from t, table( cast( multiset( select level - 1 from dual connect by level <= length(str) - length(replace(str,'@')) ) as sys.OdciNumberList ) ) / COL1 COL2 COL3 ------------------------------------ ------------------------------------ ------ A B C A B C A B C A B C A B C A B C 6 rows selected. SQL>
Solomon Yakobson wrote:ok thanks will do.I struggle with regular expressions.Any tips?
Well, regexp isn't Oracle's "invention", so you can read almost any regexp tutorial (obviously, Oracle supports just small subset of what regexp can do). Personally, I knew regexp from old C programming days, so I wasn't looking for "modern" tutorials. Sorry, can't help you with that.
SY.
Rahul India wrote:Perhaps look in the FAQ?Solomon Yakobson wrote:ok thanks will do.I struggle with regular expressions.Any tips?
Well, regexp isn't Oracle's "invention", so you can read almost any regexp tutorial (obviously, Oracle supports just small subset of what regexp can do). Personally, I knew regexp from old C programming days, so I wasn't looking for "modern" tutorials. Sorry, can't help you with that.
SY.