Example: A 23   User defined function to map SUBSTRING_INDEX

create function SUBSTRING_INDEX(In varchar(2000),delimit varchar(200), n Int)

returns varchar(2000)

deterministic no external action contains sql 

begin atomic 

declare out varchar(2000);

declare dem varchar(2000);

declare num int;

declare pos int;

declare temp varchar(2000);

set dem=delimit;

set temp=In;

set num=n;

set pos=1;

if(num<0) then

while(locate(delimit,temp)!=0) do

set temp=substr(temp,locate(delimit,temp)+1);

set num=num+1;

end while;

set num=num+1;

set temp=In; 

end if;

while (num>0) do

set pos=pos+locate(delimit,temp) 1;

set temp=substr(temp,locate(delimit,temp)+1);

set num=num 1;

end while; 

if(n>0) then 

return substr(In,1,pos); 

else

return substr(In,pos+1);

end if;

end

 Appendix A. Sample code for user defined functions 

361