转自：http://www.myexception.cn/operating-system/480929.html
- --
- --instrfunctionsthatmimicOracle'scounterpart
- --Syntax:instr(string1,string2,[n],[m])where[]denotesoptionalparameters.
- --
- --Searchesstring1beginningatthenthcharacterforthemthoccurrence
- --ofstring2.Ifnisnegative,searchbackwards.Ifmisnotpassed,
- --assume1(searchstartsatfirstcharacter).
- CREATEFUNCTIONinstr(varchar,varchar)RETURNSintegerAS$$
- DECLARE
- posinteger;
- BEGIN
- pos:=instr($1,$2,1);
- RETURNpos;
- END;
- $$LANGUAGEplpgsqlSTRICTIMMUTABLE;
- FUNCTIONinstr(stringinteger)
- AS$$
- DECLARE
- integerNOTNULLDEFAULT0;
- temp_strvarchar;
- beginteger;
- lengthss_lengthBEGIN
- IFbeg_index>0THEN
- temp_str:=substring(stringFROMbeg_index);
- pos:=position(string_to_searchINtemp_str);
- IFpos=0RETURN0;
- ELSE
- RETURNpos+beg_index-1;
- ENDIF;
- ELSE
- ss_length:=char_length(string_to_search);
- length:=char_length(string);
- beg:=length+beg_index-ss_length+2;
- WHILEbeg>0LOOP
- FROMbegFORss_length);
- IFpos>0RETURNbeg;
- beg:=beg-1;
- ENDLOOP;
- beg_indexinteger,occur_indexinteger)
- DEFAULT0;
- occur_numberiTHEN
- beg:=beg_index;
- FORiIN1..occur_indexLOOP
- IFi=1beg:=beg+pos-1;
- beg:=beg+pos;
- FROMbeg+1);
- RETURN0;
- RETURNbeg;
- ENDIF;
- ss_length:=char_length(string_to_search);
- length:=char_length(string);
- beg:=length+beg_index-ss_length+2;
- WHILEbeg>0LOOP
- FORss_length);
- pos:=position(string_to_searchINtemp_str);
- occur_number:=occur_number+1;
- IFoccur_number=occur_indexbeg:=beg-1;
- ENDLOOP;
- END;
- $$LANGUAGEplpgsqlSTRICTIMMUTABLE;
