instr

Database Type and Version

• Source database type and version: all Oracle versions
• Target database type and version: GaussDB of versions earlier than V2.0-2.7

Function replacement syntax:

CREATE OR REPLACE FUNCTION dsc_ora_ext.dsc_fn_instr
/* 1st param (i_str) is the text expression to search
   2nd param (i_substr) is the string to search for
   3rd param (i_startpos) is a nonzero INTEGER indicating where in string 
       the function begins the search. When it is negative, then INSTR counts
	   and searches backward from the end of string. Default value is 1.
   4th param (i_occurrence)	An INTEGER indicating which occurrence of string 
       the function should search for. It must be positive; Default value is 1.
*/ 
     ( i_str           IN   TEXT
	 , i_substr        IN   TEXT
	 , i_startpos      IN   INTEGER = 1
	 , i_occurrence    IN   INTEGER = 1 )
RETURN INTEGER 
IMMUTABLE
AS 
      v_search_str        TEXT;
      v_shift             INTEGER := 0;
      v_pos               INTEGER;
BEGIN
    IF i_str IS NULL OR i_substr IS NULL THEN
    -- if source string or substring is empty 
	   RETURN NULL;
    END IF;

    IF i_occurrence <= 0 THEN
	   RAISE EXCEPTION 'ERROR-01428: argument -1 is out of range';
	END IF;
    IF i_startpos = 0 THEN
        RETURN 0;
    END IF;

    /* When i_startpos is negative, then the function should count and 
	   searches backward from the end of string. */
    IF i_startpos < 0 THEN
        i_str    := REVERSE(i_str);
        i_substr := REVERSE(i_substr);
        v_pos    := -1 * i_startpos;
    ELSE
        v_pos := i_startpos;
    END IF;

    FOR idx IN 1..i_occurrence 
	LOOP
        v_shift      := v_shift + v_pos;
        v_search_str := SUBSTR(i_str, v_shift);
        v_pos        := STRPOS(v_search_str, i_substr);
        IF v_pos = 0 THEN
            RETURN 0;
        END IF;
    END LOOP;

    IF i_startpos > 0 THEN
       RETURN v_pos + v_shift - 1;
    ELSE
       RETURN LENGTH(i_str) - LENGTH(i_substr) - v_pos - v_shift + 3;
    END IF;
END;