Oracle 对字符串列中的串联数字列表进行排序

您好，我在表中有一列当前值，例如 “29 | 1 | 3004 | 3 | 2 | 24”
我想将值按顺序重新排列为 '| 1 | 2 | 3 | 24 | 29 | 3004'
我正在尝试在不创建临时表或任何保留位置的情况下执行此操作，是否可以在1个SQL语句中执行此操作？
这应该适用于多个记录表

SQL> with t as
  2   ( select 1 pk, '29|1|3004|3|2|24' str from dual
  3     union all
  4     select 2 pk, '2|17|304|32|21|24' str from dual
  5     ),
  6  d as
  7   ( select '|' delim from dual ),
  8  list_as_rows as
  9  (
 10  select  pk, to_number(substr( delim||str||delim ,
 11                 instr( delim||str||delim , delim ,1,idx)+ length(delim) ,
 12                 instr( delim||str||delim , delim ,1,idx+1) -
 13                 instr( delim||str||delim , delim ,1,idx) -length(delim))) item
 14      from t,
 15           d,
 16         lateral( select level idx
 17                 from dual
 18                 connect by level <= (length(str) -
 19                length(replace(str,delim)))/length(delim) +1)
 20  )
 21  select pk, listagg(item,'|') within group ( order by item ) as rearranged
 22  from list_as_rows
 23  group by pk;

        PK REARRANGED
---------- ------------------------------
         1 1|2|3|24|29|3004
         2 2|17|21|24|32|304

2 rows selected.