楼主问问题最好说明白一点,比如有2个-,就把-替换为4个0 之类的说明下。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;
很好,佩服不是我想要的啊!!! 11g 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;
(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;
11g
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;