instr関数コード - lookworld @ 簡単メモ～

--- 
DROP FUNCTION instr(VARCHAR,VARCHAR);
 
CREATE FUNCTION instr(VARCHAR,VARCHAR) RETURNS INTEGER AS '
DECLARE
    pos integer;
BEGIN
    pos:= instr($1,$2,1);
    RETURN pos;
END;
' LANGUAGE 'plpgsql';
 
--
DROP FUNCTION instr(VARCHAR,VARCHAR,INTEGER);
CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER) RETURNS INTEGER AS '
DECLARE
    string ALIAS FOR $1;
    string_to_search ALIAS FOR $2;
    beg_index ALIAS FOR $3;
    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';
 
--
DROP FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER);
CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS '
DECLARE
    string ALIAS FOR $1;
    string_to_search ALIAS FOR $2;
    beg_index ALIAS FOR $3;
    occur_index ALIAS FOR $4;
    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';