oracle split() 函數

標籤：pre   select   varchar   _id   instr   creat   where   auth   bsp   

 1 create type tabletype is table of varchar2(3000); 2 CREATE OR REPLACE FUNCTION split (p_list CLOB, p_sep VARCHAR2 := ‘,‘) 3 RETURN tabletype 4 PIPELINED 5 /************************************** 6 * Name: split 7 * Author: Sean Zhang. 8 * Date: 2012-09-03. 9 * Function: 返回字串被指定字元分割後的表類型。10 * Parameters: p_list: 待分割的字串。11 p_sep: 分隔字元，預設逗號，也可以指定字元或字串。12 * Example: SELECT *13 FROM users14 WHERE u_id IN (SELECT COLUMN_VALUE15 FROM table (split (‘1,2‘)))16 返回u_id為1和2的兩行資料。17 **************************************/18 IS19 l_idx PLS_INTEGER;20 v_list VARCHAR2 (32676) := p_list;21 BEGIN22 LOOP23 l_idx := INSTR (v_list, p_sep);24 IF l_idx > 025 THEN26 PIPE ROW (SUBSTR (v_list, 1, l_idx - 1));27 v_list := SUBSTR (v_list, l_idx + LENGTH (p_sep));28 ELSE29 PIPE ROW (v_list);30 EXIT;31 END IF;32 END LOOP;33 END;

 

 

--test code select * from table(split(‘1,2,3,4,5,6‘,‘,‘));

 

oracle split() 函數