先替换非数字的为空，再截取
with tmp_a as (
select '2018-10-15 12:30:59' update_time from dual union all --结果是 20181015
select '2017/10/16 12:30:59' update_time from dual union all --结果是 20171016
select '20151015 12:30:59' update_time from dual union all --结果是 20151015
select null update_time from dual union all --结果是 空
select '201510' update_time from dual union all --结果是 201510
select '20131015123059' update_time from dual --结果是 20131015
)
select substr(regexp_replace(update_time,'[^0-9]'),1,8) 截取前8个数字 from tmp_a