ORACLE字符串拆分

2014-07-26

方法1 - 创建拆分函数

ORACLE的函数不能像MSSQL那样支持直接返回表类型，所以要先创建一种自定义类型。这里用到的是嵌套表（Nested Table）。

-- Nested Table
CREATE OR REPLACE TYPE split_str IS TABLE OF VARCHAR(100);
/

-- Function
CREATE OR REPLACE FUNCTION fn_Split
(
    p_Str VARCHAR2,
    p_Delimiter VARCHAR2
)
RETURN split_str PIPELINED
AS
    v_Str VARCHAR(4000) := p_Str;
    v_Index NUMBER;
    v_SubLength NUMBER;
BEGIN
    -- p_Delimiter is null return empty result
    IF p_Delimiter IS NULL THEN
        RETURN;
    END IF;

    v_index := INSTR(v_Str,p_Delimiter);
    WHILE v_Index <> 0
    LOOP
        PIPE ROW(SUBSTR(v_Str,1,v_Index-1));
        v_SubLength := LENGTH(v_Str) - (v_index + LENGTH(p_Delimiter) - 1);
        v_Str := SUBSTR(v_Str,-v_SubLength,v_SubLength);
        v_index := INSTR(v_Str,p_Delimiter);
    END LOOP;
    PIPE ROW(v_Str);
    RETURN;
END;
/

-- 调用（更推荐第二种方式）
SELECT fn_split('This@@is@@it','@@') FROM dual;
SELECT * FROM Table(fn_split('This@@is@@it','@@'));

方法2 - 用 REGEXP_SUBSTR 实现

举例如下：

-- 按一个或多个@符号分割
SELECT
  REGEXP_SUBSTR('this@is@@a@test', '[^@]+', 1, level) AS parts
FROM dual
CONNECT BY REGEXP_SUBSTR('this@is@a@test', '[^@]+', 1, level) IS NOT NULL;

-- 按两个@@分割
SELECT
  REGEXP_SUBSTR('this@is@@a@@test', '(.*?)(@@|$)', 1, level, NULL, 1) AS parts
FROM dual
CONNECT BY REGEXP_SUBSTR('this@is@@a@@test', '(.*?)(@@|$)', 1, level, NULL, 1) IS NOT NULL;

如何理解这里的 connect by 呢？它相当于一个行生成器，不断地生成新行，直到 connect by 后面的条件不再满足。

这种方法更灵活，可实现按一个或多个分隔符拆分的逻辑，但可读性略差，逻辑不够直观，且依赖正则表达式，在数据量大的情况下性能无法保证，适合小数据量或者在无法创建UDF的情况下使用。