-- -- instr functions that mimic Oracle's counterpart -- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional parameters. -- -- Searches string1 beginning at the nth character for the mth occurrence -- of string2. If n is negative,search backwards. If m is not passed, -- assume 1 (search starts at first character). --
CREATE FUNCTION instr(varchar,varchar) RETURNS integer AS $$ DECLARE pos integer; BEGIN pos:= instr($1,$2,1); RETURN pos; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION instr(string varchar,string_to_search varchar,beg_index integer) RETURNS integer AS $$ 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; 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 RETURN beg; END IF;
beg := beg - 1; END LOOP;
RETURN 0; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION instr(string varchar, beg_index integer,occur_index integer) RETURNS integer AS $$ 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; $$ LANGUAGE plpgsql STRICT IMMUTABLE; (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|