在Oracle中要截取类似0.2g*2ml*10支*300盒中的容量,研究好久,终于完成了,写了个function,供参考
create or replace function query_colume(material_spec in varchar2)
return number as
f_colume number;
f_upper varchar2(50);
f_front varchar2(20);
f_number varchar2(20);
begin
select Upper(material_spec) into f_upper from dual; --将ml变为大写
select substr(f_upper, 0, instr(f_upper, 'ML') - 1)
into f_front
from dual; --截取ML前面的部分
select translate(f_front,
translate(f_front, '0123456789', ' '),
' ')
into f_number
from dual; --取出字符串中所有的数字;
select to_number(substr(trim(f_number), instr(trim(f_number), ' ', -1, 1)))
into f_colume
from dual;--取出最后一个空格后面的部分,并转换成数字
return f_colume;
end;
materi_spec 为要传入的字符串