Oracle
create or replace function checks(v_a varchar2,v_b varchar) return number as num number; cou number; begin num := -1; cou:=0; for i in 1..length(v_b) loop if instr(v_a,substr(v_b,i,1))>0 then cou:=cou+1; end if; end loop; if cou=length(v_b) then return cou; end if; dbms_output.put_line(cou||' '||length(v_b)); return num; end;
create or replace function GetNumByCharInString(p_string in varchar2, p_char in varchar2) return number is v_ret number; v_num1 number; v_num2 number; v_num3 number; begin begin v_num1 := Length(p_string); v_num2 := Length(replace(p_string, p_char)); v_num3 := Length(p_char); v_ret := (v_num1 - v_num2) / v_num3; exception when others then v_ret := 0; end; return v_ret; end GetNumByCharInString;
create or replace function GetSubstrBySep(p_sourceString varchar2, p_destString varchar2, p_appearPosition number) return varchar2 is v_ret varchar2(2000):=''; v_num1 number; v_num2 number; v_temp varchar2(2000):=''; begin begin v_temp:=p_destString||p_sourceString||p_destString; v_num1:=Instr(v_temp,p_destString,1,p_appearPosition)+1; v_num2:=Instr(v_temp,p_destString,1,p_appearPosition+1)-Instr(v_temp,p_destString,1,p_appearPosition)-1; v_ret:=Substr(v_temp, v_num1, v_num2); exception when others then v_ret:=''; end; return v_ret; end GetSubstrBySep;
可以将这些函数和instr混合使用达到你自己的目的
function GetNumByCharInString(p_string in varchar2, p_char in varchar2);这个函数在p_string和p_char都为一个数字时返回结果为空,可以将p_string变成‘,’||p_string||‘,’