SQL> with test (col) as
2 (select 'MERGE|WorkRelationship|||2020/12/31|RESIGNATION|Y||XYZ Limited|12345A' from dual
3 union all
4 select 'Little|Foot' from dual
5 )
6 select rtrim(regexp_substr(col, '[^|]*\|?', 1, column_value), '|') val
7 from test cross join
8 table(cast(multiset(select level from dual
9 connect by level <= regexp_count(col, '\|') + 1
10 ) as sys.odcinumberlist))
11 order by col, column_value;
VAL
------------------------------
Little
Foot
MERGE
WorkRelationship
2020/12/31
RESIGNATION
Y
XYZ Limited
12345A
12 rows selected.
SQL>
2条答案
按热度按时间ajsxfq5m1#
一个选择是
regexp_substr()
. 以下是一种考虑到分隔字符串中可能存在空槽的方法:db小提琴演示:
nfg76nw02#
如果您要拆分的不仅仅是一个字符串,而且它包含数量可变的子字符串,这可能会有所帮助: