Explode function for pl/sql and hour changeing to minutes

May 30th, 2010 | by admin |

First create the type with following query.

CREATE OR REPLACE
TYPE listtable AS TABLE OF VARCHAR2 (255);
/

This is the function to explode the string.
For example we have 4:45
‘:’ is p_seperator and 4:45 is p_string and result of this function will be 285.

CREATE OR REPLACE FUNCTION explode (p_seperator IN VARCHAR2, p_string IN VARCHAR2)
RETURN listtable AS
l_string LONG DEFAULT p_string || p_seperator;
l_data listtable := listtable ();
n NUMBER;
v_min varchar2(10);
v_1 VARCHAR2(10);
BEGIN
LOOP
EXIT WHEN l_string IS NULL;
n := INSTR (l_string, p_seperator);
l_data.EXTEND;
v_1:=LTRIM (RTRIM (SUBSTR (l_string, 1, n - 1)));
----------
l_data (l_data.COUNT) := v_1;
---------
l_string := SUBSTR (l_string, n + 1);
END LOOP;
-- v_min:=l_data(1)*60+l_data(2);
RETURN l_data;
END;

Post a Comment