最近参与到了公司渠道考核项目的实施，由于是新加入，对于很多东西都不是很熟悉，这不遇到一个难题，需要从一堆有汉字有英文还有数字的字符串里面把数字取出来，数其中有一个可以利用的规律是数字前一定是冒号 : 而且数字是四位数的一开始用instr函数和substr函数来查找，但是于对一行字符串当中有多个冒号的情况未能为力，必须要知道一行当中有多少个冒号，才能取出所有的数字。一开始想着假设一行不会超过10个，然后用instr查找十次，找出来的值用substr取出来然再判断是否可以转换为number型的，可以转换的话就是数字就保留下来存放在一个结果表里面，不能转换的话就是字符，就给exception掉。虽然这种方法可行，但是终究不够严谨，假设有超过10个冒号就会丢失信息。

后来一个同事说可以用替换函数找到冒号给替换成空值，我恍然大悟，这样用length函数求出总长度然后再求出REPLACE替换为空值后的长度，相减即可得到冒号的数量，当时精神为之一振，不得不佩服同事的算法确实很高明，于是按照这个思路很快就完成了一个PL/SQL语句。

附上代码，供大家参考：

create or replace
FUNCTION fc_return_numeral(in_char varchar)
RETURN number
AS
out_v number;
in_exp varchar2(4000);
BEGIN
in_exp:=in_char;
select (length(in_exp)-length(replace(in_exp,':',''))) into out_v from dual;

RETURN out_v;

END;

/

declare n number;
v1 number;
vcn1 varchar2(255);
v2 number;
vcn2 varchar2(255);
v3 varchar2(4000);
n_value number;
cursor v is select x.assess_model_id,x.assess_model_name ,y.assess_id,o.assess_name,z.fore_rules_content_lower||z.rules_content_higher||z.fore_rules_content_lower||z.fore_rules_content_higher as m_value
from cs_assess_model x
LEFT JOIN cs_assess_model_rel y ON x.assess_model_id = y.assess_model_id
LEFT JOIN cs_assess o ON y.assess_id = o.assess_id
LEFT JOIN cs_rules z ON y.rules_seq_id = z.seq_id
WHERE x.state IN (1,2)
AND o.assess_type=1
ORDER BY x.assess_model_id,y.assess_id;
begin
open v;
loop
fetch v into v1,vcn1,v2,vcn2,v3;
   exit when v%notfound;
   select fc_return_numeral(v3) into n from dual;
    for i in 1..n loop
   select to_number(substr(v3,instr(v3,':',1,i)+1,4)) into n_value from dual;
   execute immediate '
     insert into test_zyp (ASSESS_MODEL_ID,ASSESS_MODEL_NAME,ASSESS_ID,ASSESS_NAME,EXP_VALUE)
     values('||v1||','''||vcn1||''','||v2||','''||vcn2||''','||n_value||')';
   commit;
    end loop;
end loop;
end;
/

ITPub博客

巧取字符串中的数字

最新文章