create or replace function instr(str text, sub text, startpos int = 1, occurrence int = 1)
returns int language plpgsql
as $$
declare
tail text;
shift int;
pos int;
i int;
begin
shift:= 0;
if length(str) = 0 then
return null;
end if;
if startpos = 0 or occurrence <= 0 then
return 0;
end if;
if startpos < 0 then
str:= reverse(str);
sub:= reverse(sub);
pos:= -startpos;
else
pos:= startpos;
end if;
for i in 1..occurrence loop
shift:= shift+ pos;
tail:= substr(str, shift);
pos:= strpos(tail, sub);
if pos = 0 then
return 0;
end if;
end loop;
if startpos > 0 then
return pos+ shift- 1;
else
return length(str)- length(sub)- pos- shift+ 3;
end if;
end $$;
postgresql自定义函数替换oracle的instr函数
最新推荐文章于 2023-09-12 14:21:04 发布