--根据分隔符返回分隔的组数

CREATE OR REPLACE         FUNCTION get_StrArrayLength(STR VARCHAR(2000),SPLIT1 VARCHAR(10))                 RETURN INT         AS                 V_LOCATION INT;                 V_START    INT;                 V_LENGTH   INT;         BEGIN                 STR:=LTRIM(RTRIM(STR));                 V_LOCATION :=LOCATE(SPLIT1,STR);                 V_LENGTH  :=1;                 WHILE V_LOCATION <>0                 LOOP                         V_START:=V_LOCATION+1;                         V_LOCATION   :=LOCATE(SPLIT1,STR,V_START);                         V_LENGTH  :=V_LENGTH+1;                 END LOOP;                 RETURN V_LENGTH;         END;

 

--取出根据某个字符切割后的第n个字符串

CREATE OR REPLACE         FUNCTION get_StrArrayStrOfIndex(STR VARCHAR(2000),SPLIT1 VARCHAR(10),                 INDEX1   INT)                 RETURN VARCHAR(2000)         AS                 V_LOCATION INT;                 V_START    INT;                 V_NEXT     INT;                 V_SEED     INT;         BEGIN                 STR:=LTRIM(RTRIM(STR));                 V_START    :=1;                 V_NEXT :=1;                 V_SEED :=LEN(SPLIT1);                 V_LOCATION :=LOCATE(SPLIT1,STR);                 WHILE V_LOCATION  <>0 AND INDEX1 >V_NEXT                 LOOP                         V_START:=V_LOCATION+V_SEED;                         V_LOCATION :=LOCATE(SPLIT1,STR,V_START);                         V_NEXT :=V_NEXT+1;                 END LOOP;                 IF V_LOCATION=0 THEN                         V_LOCATION :=LEN(STR)+1;                 END IF;                 RETURN SUBSTRING(STR,V_START,V_LOCATION-V_START);         END;

 

--调用方法

SELECT get_StrArrayLength('8a,9c,4c,5K',',') FROM SYSDBA.SYSDUAL ;

SELECT get_StrArrayStrOfIndex('8a,9c,4c,5K',',',3) FROM SYSDBA.SYSDUAL;