Oracle使用split和splitstr函數批量分隔字串

標籤：

1.建立一個表類型

tabletype： create or replace type tabletype as table of VARCHAR2(32676);

 

2.建立split  函數

CREATE OR REPLACE FUNCTION split (p_list CLOB, p_sep VARCHAR2 := ‘,‘)RETURN tabletypePIPELINEDIS/* * 2015-11-11 * Function: 返回字串被指定字元分割後的表類型。* Parameters: p_list: 待分割的字串。p_sep: 分隔字元，預設逗號，也可以指定字元或字串。 */l_idx PLS_INTEGER;v_list VARCHAR2 (32676) := p_list;BEGINLOOPl_idx := INSTR (v_list, p_sep);IF l_idx > 0THENPIPE ROW (SUBSTR (v_list, 1, l_idx - 1));v_list := SUBSTR (v_list, l_idx + LENGTH (p_sep));ELSEPIPE ROW (v_list);EXIT;END IF;END LOOP;END; 

3.建立splitstr 函數

CREATE OR REPLACE FUNCTION splitstr (str IN CLOB,i IN INT,sep VARCHAR2 := ‘,‘)RETURN VARCHAR2/* * 2015-11-11* Function: 返回字串被指定字元分割後的指定節點字串。* Parameters: str: 待分割的字串。i: 返回第幾個節點。當i為0返回str中的所有字元，當i 超過可被分割的個數時返回空。sep: 分隔字元，預設逗號，也可以指定字元或字串。當指定的分隔字元不存在於str中時返回sep中的字元。 */ISt_i NUMBER;t_count NUMBER;t_str VARCHAR2 (400);BEGINIF i = 0THENt_str := str;ELSIF INSTR (str, sep) = 0THENt_str := sep;ELSESELECT COUNT (*) INTO t_count FROM TABLE (split (str, sep));IF i <= t_countTHENSELECT strINTO t_strFROM (SELECT ROWNUM AS item, COLUMN_VALUE AS strFROM TABLE (split (str, sep)))WHERE item = i;END IF;END IF;RETURN t_str;END;

 

4.example 

select split(‘1,2‘) from dual;select splitstr(‘abc,def‘, 2) as str from dual;select * from table (split(‘1,2‘,‘,‘));

 

5.參考出處

http://www.cr173.com/html/18046_1.html

 

Oracle使用split和splitstr函數批量分隔字串