oracle instr mysql_MySql中代替Oracle的instr方法 | 学步园

-- Function "INSTR2" DDL

CREATE FUNCTION `INSTR2`(v_string varchar(5000), v_delimiter varchar(20), pos int,nth int) RETURNS varchar(5000)

begin

declare icount int default 0;

declare len int default 0;

declare len1 int default 0;

declare lth int default 0;

declare lth1 int default 0;

declare str1 varchar(5000) default '';

set len = length(v_string);

set len1 = length(v_delimiter);

set lth = instr(v_string ,v_delimiter);

if lth=0 then

set icount = lth;

else

if pos is null then

set icount = lth;

elseif pos < 0 then

set icount = locate(v_delimiter,v_string,len+pos-1);

elseif pos = 0 then

set icount = 0;

elseif pos = 1 then

if nth is null then

set icount = lth;

elseif nth >=1 then

set icount = if (length(substring_index(v_string ,v_delimiter,nth))=0 or length(substring_index(v_string,v_delimiter ,nth))=length(v_string ),0,length(substring_index(v_string ,v_delimiter,nth))+1);

else

set icount = 0;

end if;

else

if (nth is null) or (nth=1) then

set icount = locate(v_delimiter,v_string,pos);

elseif nth > 1 then

set str1 = substring(v_string,pos) ;

set icount = if (length(substring_index(str1 ,v_delimiter,nth))=0 or length(substring_index(str1,v_delimiter ,nth))=length(str1),0,length(substring_index(str1 ,v_delimiter,nth)));

if icount<>0 then

set icount = icount+pos;

end if;

end if;

end if;

end if;

return icount;

end;

转自：http://pangxin630.blog.163.com/blog/static/2630889420092443218927/