PostgreSQL仿照Oracle的instr函数

来源：互联网发布：不用网络的斗地主编辑：程序博客网时间：2024/06/06 00:25
转自：http://www.myexception.cn/operating-system/480929.html
PostgreSQL模仿Oracle的instr函数
---- 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, string_to_search 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;
0 0