17,140
社区成员




with t as
(select '1002-1-3' subject_id
from dual
union all
select '1002-10'
from dual
union all
select '1002-2' from dual)
select listagg(subject_id1, '') within group(order by rownum)
from (select subject_id,
lpad(regexp_substr(subject_id, '[^-]+', 1, level), 4, '0') subject_id1
from t
connect by subject_id = prior subject_id
and level <= regexp_count(subject_id, '-') + 1
and prior dbms_random.value is not null)
group by subject_id;
with t as
(select '1002-1-1' subject_id
from dual
union all
select '1002-10' from dual)
select replace(subject_id,
'-',
rpad('0', regexp_count(subject_id, '-') * 2, '0'))
from t;