PostgreSQL仿照Oracle的instr函数

梦里梦外; 2022-07-11 12:20 561阅读 0赞

转自：[http://www.myexception.cn/operating-system/480929.html][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;

[http_www.myexception.cn_operating-system_480929.html]: http://www.myexception.cn/operating-system/480929.html