select t.str,replace(regexp_substr(str,'[0-9]+元'),'元','') mony from t
测试：
sys@STARTREK> with t as (
2 select '苹果2元' as str from dual
3 union all
4 select '一盒饼干43元' as str from dual
5 union all
6 select '一瓶茅台800元' as str from dual
7 )
8 select t.str,replace(regexp_substr(str,'[0-9]+元'),'元','') mony from t
9 /
STR MONY
------------- --------------------------
苹果2元 2
一盒饼干43元 43
一瓶茅台800元 800
