巧取字串中的數字

      最近參與到了公司 渠道考核專案的實施，由於是新加入，對於很多東西都不是很熟悉，這不遇到一個難題，需要從一堆有漢字有英文還有數字的字串裡面把數字取出來，數其中有一個可以利用的規律是數字前一定是冒號 : 而且數字是四位數的 一開始用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;
/