Oracle inversion Function

Oracle inversion Function

Oracle provides a reverse inversion function, reverse, but this function cannot be used for grouping inversion. This article provides a function that can be used for grouping inversion, as shown below:

Create or replace function REVERSE_F (p_str VARCHAR2, p_delimiter VARCHAR2: = '') RETURN VARCHAR2 IS v_return VARCHAR2 (4000); vp_str VARCHAR2 (4000): = p_str; v_idx number; vp_len number: = length (p_delimiter); BEGIN if (p_delimiter is null) then select reverse (p_str) into v_return from dual; else loop v_idx: = instr (vp_str, p_delimiter ); if (v_idx = 0) then v_return: = vp_str | v_return; exit; elsif (v_idx = 1) then v_return: = substr (vp_str, 1, vp_len) | v_return; vp_str: = substr (vp_str, vp_len + 1); else v_return: = substr (vp_str, 1, v_idx-1) | v_return; vp_str: = substr (vp_str, v_idx ); end if; end loop; end if; RETURN v_return; END REVERSE_F;