Here is the function:

create or replace FUNCTION FUNC_PART(
  p_TEXT varchar2,
  p_COLUMN number,
  p_SEPARATOR varchar2
) RETURN varchar2 AS
  v_POS_  number;
  v_POS2    number;
  V_COLUMN NUMBER;
BEGIN
  V_COLUMN:=p_COLUMN;
  v_POS_ := 1;
  v_POS2  := INSTR(p_TEXT, p_SEPARATOR, v_POS_);
  WHILE (V_COLUMN >1 AND v_POS2> 0) LOOP
       v_POS_ := v_POS2   1;
       v_POS2 := INSTR(p_TEXT, p_SEPARATOR, v_POS_);
       V_COLUMN :=V_COLUMN - 1;
  END LOOP;
  IF V_COLUMN > 1 THEN
    v_POS_ := LENGTH(RTRIM(p_TEXT))   1;
  END IF;
  IF v_POS2 = 0 THEN
    v_POS2 := LENGTH(RTRIM(p_TEXT))   1;
  END IF;
  RETURN SUBSTR (p_TEXT, v_POS_, v_POS2 - v_POS_);
END;

CodePudding user response：

I did not understand this code carefully, but from the results, the following two pieces of code have the same meaning.

select FUNC_PART('asdfghsdfg', 3, 's')
  from dual;
  
select regexp_substr('asdfghsdfg', '[^s] ', 1, 3)
  from dual;

This is likely to be migrated from other databases to the code in oracle, because oracle does not need such a custom function