UPDATE tablename SET PARENTID=(
case
WHEN LENGTH(column1)/2=1 then '0'
when LENGTH(column2)/2=2 then substr(column1,1,2)
when LENGTH(column3)/2=3 then substr(column2,1,4)
when LENGTH(column4)/2=4 then substr(column3,1,6)
end
)
select
userid,
substr(userid,
instr(',' || userid || ',', ',', 1, n),
instr(',' || userid || ',', ',', 1, n + 1) -
instr(',' || userid || ',', ',', 1, n) - 1) aa
from xt_jbpm_dbsxb, (select level n from dual connect by level <= 10) x
where substr(userid,
instr(',' || userid || ',', ',', 1, n),
instr(',' || userid || ',', ',', 1, n + 1) -
instr(',' || userid || ',', ',', 1, n) - 1) is not null
and id=77823
order by n
case
WHEN LENGTH(column1)/2=1 then '0'
when LENGTH(column2)/2=2 then substr(column1,1,2)
when LENGTH(column3)/2=3 then substr(column2,1,4)
when LENGTH(column4)/2=4 then substr(column3,1,6)
end
)
select
userid,
substr(userid,
instr(',' || userid || ',', ',', 1, n),
instr(',' || userid || ',', ',', 1, n + 1) -
instr(',' || userid || ',', ',', 1, n) - 1) aa
from xt_jbpm_dbsxb, (select level n from dual connect by level <= 10) x
where substr(userid,
instr(',' || userid || ',', ',', 1, n),
instr(',' || userid || ',', ',', 1, n + 1) -
instr(',' || userid || ',', ',', 1, n) - 1) is not null
and id=77823
order by n