3,492
社区成员
发帖
与我相关
我的任务
分享
select id, nm || (level + fr_num - 1)
from (SELECT t1.id id,
regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
regexp_substr(t1.name, '[0-9]+', 1, 1) fr_num,
regexp_substr(t1.name, '[0-9]+', 1, 2) to_num
FROM T1)
connect by level <= to_num - fr_num + 1
and id = prior id
and prior dbms_random.value is not null;
[/quote]
谢谢指正,我修改了一下,见#9select id, nm || (level + fr_num - 1)
from (SELECT t1.id id,
regexp_substr(t1.name, '[[:alpha:]]+', 1, 1) nm,
regexp_substr(t1.name, '[[:digit:]]+', 1, 1) fr_num,
regexp_substr(t1.name, '[[:digit:]]+', 1,2) to_num
FROM T1)
connect by level <= to_num - fr_num + 1
and id = prior id
and prior dbms_random.value is not null;
select id, nm || (level + fr_num - 1)
from (SELECT t1.id id,
regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
regexp_substr(t1.name, '[0-9]+', 1, 1) fr_num,
regexp_substr(t1.name, '[0-9]+', 1, 2) to_num
FROM T1)
connect by level <= to_num - fr_num + 1
and id = prior id
and prior dbms_random.value is not null;
select id, nm || (level + fr_num - 1)
from (SELECT t1.id id,
regexp_substr(t1.name, '[[:alpha:]]', 1, 1) nm,
regexp_substr(t1.name, '[[:digit:]]', 1, 1) fr_num,
regexp_substr(t1.name, '[[:digit:]]', 1, 2) to_num
FROM T1)
connect by level <= to_num - fr_num + 1
and id = prior id
and prior dbms_random.value is not null;