Postgresql数据库实现oracle的instr命令

一、instr()函数介绍

1、instr()函数的格式 （俗称：字符查找函数）
格式一：instr( string1, string2 ) / instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束（转载）。

二、Postgresql中的的实现

1、类比oracle两个参数的insert很容易实现，类比如下

oracle：instr(code, '-')      

pg：position('-' in  code)   或 strpos(code, '-')

2、其他三位参数，四位参数pg库并没有相对应的函数，所以需要构造函数，内容如下

--三位参数
CREATE OR REPLACE FUNCTION public.instr3(string character varying, string_to_search character varying, beg_index integer)
 RETURNS integer
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$        
DECLARE pos integer NOT NULL DEFAULT 0;
    temp_str  varchar;
    beg       integer;
    length    integer;
    ss_length integer;
  BEGIN
    IF beg_index > 0 THEN
      temp_str := substring(string FROM beg_index);
      pos      := position(string_to_search IN temp_str);
    
      IF pos = 0 THEN
        RETURN 0;
      ELSE
        RETURN pos + beg_index - 1;
      END IF;
    ELSIF beg_index < 0 THEN
      ss_length := char_length(string_to_search);
      length    := char_length(string);
      beg       := length + beg_index - ss_length + 2;
      WHILE beg > 0 LOOP
        temp_str := substring(string FROM beg FOR ss_length);
        pos      := position(string_to_search IN temp_str);
        IF pos > 0 THEN
          RETURN beg;
        END IF;
        beg := beg - 1;
      END LOOP;
      RETURN 0;
    ELSE
      RETURN 0;
    END IF;
  END;
  $function$
;

--四位参数
CREATE OR REPLACE FUNCTION public.instr4(string character varying, string_to_search character varying, beg_index integer, occur_index integer)
 RETURNS integer
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$
DECLARE
pos integer NOT NULL DEFAULT 0;
occur_number integer NOT NULL DEFAULT 0;
temp_str varchar;
beg integer;
i integer;
length integer;
ss_length integer;
BEGIN
IF beg_index > 0 THEN
beg := beg_index;
temp_str := substring(string FROM beg_index);
FOR i IN 1..occur_index LOOP
pos := position(string_to_search IN temp_str);
IF i = 1 THEN
beg := beg + pos - 1;
ELSE
beg := beg + pos;
END IF;
temp_str := substring(string FROM beg + 1);
END LOOP;
IF pos = 0 THEN
RETURN 0;
ELSE
RETURN beg;
END IF;
ELSE
ss_length := char_length(string_to_search);
length := char_length(string);
beg := length + beg_index - ss_length + 2;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
pos := position(string_to_search IN temp_str);
IF pos > 0 THEN
occur_number := occur_number + 1;
IF occur_number = occur_index THEN
RETURN beg;
END IF;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
END IF;
END;
$function$
;

通过上述两个自建函数，便可以实现pg库实现instr的三个参数、四个参数的奇函数使用。如有更好方法，欢迎一起分享~