Simpler regular expression for oracle regexp_replace

As there's unique column (ID, as you said), see if this helps:

• split every column into rows
• compose them back (using listagg) which uses 2 CASEs:
  • one to remove values you don't want
  • another to properly sort them ("put value at position 16 to position 4")

Note that my result differs from yours; if I counted it correctly, 16666 isn't at position 16 but 17 so - 11512 has to be moved to position 4.

I also added another dummy row which is here to confirm whether I counted positions correctly, and to show why you have to use lines #10-12 (because of duplicates).

OK, here you are:

SQL> with test (id, col) as
  2    (
  3    select 1, '|||14444|10107|227931|10115||10118||11361|11485||10110||11512|16666|||' from dual union all
  4    select 2, '1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|20'                     from dual
  5    ),
  6  temp as
  7    (select replace(regexp_substr(col, '(.*?)(\||$)', 1, column_value), '|', '') val,
  8            column_value lvl,
  9            id
 10     from test cross join table(cast(multiset(select level from dual
 11                                              connect by level <= regexp_count(col, '\|') + 1
 12                                             ) as sys.odcinumberlist))
 13    )
 14  select id,
 15    listagg(case when lvl in (4, 6, 8, 9, 11, 12, 13) then '|'
 16                 else val || case when lvl = 20 then '' else '|' end
 17            end, '')
 18            within group (order by case when lvl = 16 then 4
 19                                        when lvl =  4 then 16
 20                                        else lvl
 21                                   end) result
 22  from temp
 23  group by id;

        ID RESULT
---------- ------------------------------------------------------------
         1 |||11512|10107||10115|||||||10110|||16666|||
         2 1|2|3|16|5||7|||10||||14|15||17|18|19|20

SQL>