Function
/**
* Split the string according to the separator.
*
* @param I_VALUES: String is divided
* @param I_SEPARATOR: Separator
* @return ARRAY_STRING: String array
*/
CREATE OR REPLACE
FUNCTION FN_SPLIT
(
I_VALUES IN VARCHAR2,
I_SEPARATOR IN VARCHAR2
) RETURN ARRAY_STRING
AS
V_STR VARCHAR2(200 );
V_SEPARATOR VARCHAR2(10 );
V_LENGTH NUMBER;
V_START NUMBER := 1 ;
V_END NUMBER;
V_INDEX NUMBER := 1 ;
O_ARRAY_STRING ARRAY_STRING;
BEGIN
O_ARRAY_STRING := ARRAY_STRING();
V_SEPARATOR := NVL(I_SEPARATOR,
V_STR := LTRIM (RTRIM (I_VALUES));
V_LENGTH := LENGTH(V_STR);
WHILE (V_START <= V_LENGTH)
LOOP
V_END := INSTR (V_STR, V_SEPARATOR, V_START);
IF V_END = 0 THEN
IF LTRIM (RTRIM (SUBSTR(V_STR, V_START))) IS NOT NULL THEN
O_ARRAY_STRING.extend;
O_ARRAY_STRING(V_INDEX) := LTRIM (RTRIM (SUBSTR(V_STR,V_START)));
V_INDEX := V_INDEX + 1 ;
END IF ;
V_START := V_LENGTH + 1 ;
ELSE
IF LTRIM (RTRIM (SUBSTR(V_STR, V_START, V_END - V_START))) IS NOT NULL THEN
O_ARRAY_STRING.extend;
O_ARRAY_STRING(V_INDEX) := LTRIM (RTRIM (SUBSTR(V_STR, V_START, V_END - V_START)));
V_INDEX := V_INDEX + 1 ;
END IF ;
V_START := V_END + 1 ;
END IF ;
END LOOP ;
END FN_SPLIT
Test
/*********** FN_SPLIT TEST *************/
DECLARE
I_VALUES VARCHAR2(200 );
I_SEPARATOR VARCHAR2(10 );
V_Return ARRAY_STRING;
BEGIN
I_VALUES := 'AAA, BB , CCC' ;
I_SEPARATOR := '' ;
V_Return := FN_SPLIT(
I_VALUES => I_VALUES,
I_SEPARATOR => I_SEPARATOR
);
FOR i IN 1 .. V_Return.count LOOP
DBMS_OUTPUT.PUT_LINE(i || ': ' || V_Return(i));
END LOOP;
END;