oracle 正则表达式转换为 substr/instr 和 case 函数

Question

请帮我用substr/instr&保持简单的case函数：

SELECT ROWNUM serlnumb,
REGEXP_SUBSTR (in_mail, '[^,]+', 1, LEVEL) emailadd
FROM   DUAL
CONNECT BY REGEXP_SUBSTR (in_mail, '[^,]+', 1, LEVEL) IS NOT NULL;

Answer 1

这实际上是您上一个问题的重复，但在输出中没有使用DISTINCT和SUBSTR。

WITH rsqfc ( mail, start_pos, end_pos ) AS (
SELECT in_mail,
1,
INSTR( in_mail, ',', 1 )
FROM   DUAL
UNION ALL
SELECT mail,
end_pos + 1,
INSTR( mail, ',', end_pos + 1 )
FROM   rsqfc
WHERE  end_pos > 0
)
SELECT CASE end_pos
WHEN 0
THEN SUBSTR( mail, start_pos )
ELSE SUBSTR( mail, start_pos, end_pos - start_pos )
END AS emailadd
FROM   rsqfc;

如果in_mail是一个值为abc,def,ghi的(绑定或PL/SQL(变量，则输出：

|电子邮件||：-------||abc||def||ghi|

但是，如果您在PL/SQL中使用它，那么它将涉及从PL/SQL范围到SQL的上下文切换。你最好写一个PL/SQL函数来分割字符串：

CREATE OR REPLACE FUNCTION split_String(
i_str    IN  VARCHAR2,
i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
p_result       SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
p_start        NUMBER(5) := 1;
p_end          NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
END IF;
END IF;
RETURN p_result;
END;
/

然后，您可以直接在PL/SQL中使用它，而不必将上下文切换到SQL:

DECLARE
in_mail VARCHAR2(20) := 'abc,def,ghi';
v_emailadds SYS.ODCIVARCHAR2LIST;
BEGIN
v_emailadds := SPLIT_STRING( in_mail );
FOR i IN 1 .. v_emailadds.COUNT LOOP
DBMS_OUTPUT.PUT_LINE( v_emailadds(i) );
END LOOP;
END;
/

哪个输出：

abc
def
ghi

db<gt；小提琴这里