一条SQL就能搞定:select id, regexp_substr(temp, '[^,]+', 1, level) from (select id, temp, rownum rn from t) t1 connect by level <= regexp_count(t1.temp, ',') + 1 and prior id = id and prior rn = rn and prior dbms_random.value is not null;
因为10g没有regexp_count函数可以采用下面的语句 select id, regexp_substr(temp, '[^,]+', 1, level) from (select id, temp, rownum rn from t) t1 connect by level <= LENGTH(temp) - LENGTH(REGEXP_REPLACE(temp, ',', ''))+1 and prior id = id and prior rn = rn and prior dbms_random.value is not null;
from (select id, temp, rownum rn from t) t1
connect by level <= regexp_count(t1.temp, ',') + 1
and prior id = id
and prior rn = rn
and prior dbms_random.value is not null;
select id, regexp_substr(temp, '[^,]+', 1, level)
from (select id, temp, rownum rn from t) t1
connect by level <= LENGTH(temp) - LENGTH(REGEXP_REPLACE(temp, ',', ''))+1
and prior id = id
and prior rn = rn
and prior dbms_random.value is not null;