oracle语句实现截取数字前内容

Oracle 11g 版本：

select wmts_id wmts_id,
substr( wmts_area,1,case when (regexp_instr( wmts_area,'[0-9]',1,1))>0 then (regexp_instr( wmts_area,'[0-9]',1,1))-1
else length(wmts_area)
end
) wmts_simplify
from t_wnms_wmts

update t_wnms_wmts a
set a.wmts_simplify=( select b.wmts_simplify
from (select wmts_id wmts_id,
substr( wmts_area,1,
case when (regexp_instr( wmts_area,'[0-9]',1,1))>0 then (regexp_instr( wmts_area,'[0-9]',1,1))-1
else length(wmts_area)
end
) wmts_simplify
from t_wnms_wmts
) b
where a.wmts_id=b.wmts_id ) ;

自己 替换成你的表名和字段就行了!

select substr(add,1,Instr(add,'100')- 1) from table

要更新的话就是

update table set add1 = substr(add,1,Instr(add,'100')- 1)

你的地址格式很固定吗？那你就直接insstr“街”的位置，然后截取不就可以了！