[Oracle]: Oracle 分隔字符串方法

1. 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

1. 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;