Help Center/ Database and Application Migration UGO/ User Guide/ Syntax Conversion/ Risky Custom Functions/ Replacing Custom Oracle Functions with GaussDB Functions/ instrb
Updated on 2025-06-07 GMT+08:00
View PDF
Share

instrb

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_instrb
/* This function is used to support Oracle INSTRB */
( i_str           IN   TEXT
, i_substr        IN   TEXT
, i_startpos      IN   INTEGER = 1
, i_occurrence    IN   INTEGER = 1 )
RETURN INTEGER
IMMUTABLE
AS
v_instr         INTEGER;
v_instrb        INTEGER;
BEGIN
v_instr := INSTR(i_str, i_substr, i_startpos, i_occurrence);

IF v_instr     <= 0 THEN
v_instrb    := 0;
ELSIF v_instr   = 1 THEN
v_instrb    := 1;
ELSE
v_instrb    := LENGTHB(LEFT(i_str, INSTR(i_str, i_substr, i_startpos, i_occurrence)-1))+1;
END IF;

RETURN v_instrb;

END;