Oracle 字符串分割函数 splitstr 和 splitstrbyseparators
本文内容
- 演示字符串分割
- 数据结构——字符数组
- 字符串分割函数 PL/SQL 包
- 结果
演示字符串分割
本文字符串分割函数能达到如下效果，这是用 SELECT 语句查看结果，在“结论”小节，将用过程查看分割的结果：
select strutil.concat(strutil.splitstr('a,1,b,2,ccdd;ef;')) as split1,
strutil.concat(strutil.splitstrbyseparators('a,1,b,2,ccdd;ef;', ',')) as split2,
strutil.concat(strutil.splitstrbyseparators('a,1,b,2,ccdd;ef;', ',;')) as split3,
strutil.concat(strutil.splitstrbyseparators('a,1,b,2,;|\;ef;', ',;|\')) as split4
from dual
结果：
a,1,b,2,ccdd;ef; a1b2ccdd;ef; a1b2ccddef a1b2ef
数据结构——字符数组
CREATE OR REPLACE TYPE chrs_tbl IS TABLE of VARCHAR2(100)
/
该字符数组用于保存分割后的字符。根据这个定义，如果分割单个字符，那没什么限制；但是如果按你指定的分隔符来分割，那就有限制了。限制是分割后的单个字符不能超过 100 个。其实，这样的限制也没什么关系，为自己系统定制一个分割函数就可以。
字符串分割函数 PL/SQL 包
包规范定义
create or replace package strutil is
-- Author : ADMINISTRATOR
-- Created : 2012/11/10 17:15:14
-- Purpose : 字符串分割
--
v_desc_chrs_tbl chrs_tbl;
--
v_length NUMBER;
v_step NUMBER;
FUNCTION splitstr(v_str IN VARCHAR2) RETURN chrs_tbl;
FUNCTION splitstrbyseparators(v_str in VARCHAR2,
v_separators in VARCHAR2) RETURN chrs_tbl;
FUNCTION concat(chrs in chrs_tbl) RETURN VARCHAR2;
PROCEDURE output(chrs in chrs_tbl);
end strutil;
包体定义
- splitstr 函数 - 按单个字符分割。利用 substr 函数，起始位置在变化（该函数第二个参数），而“步长”（该函数第三个参数）永远为 1。
- splitstrbyseparators - 按指定的字符串分割。原理同 splitstr 函数一样。利用 regexp_instr 函数匹配，将分隔符变成正则表达式，到原串匹配，以确定截取子字符串的起始位置和步长。若原串中没有指定的分割符，则按原串，否则分割。
- concat - 将分割的字符串数组连接成字符串。
- output - 输出分割的字符串数组。
create or replace package body strutil is
/* 单个字符串
v_str : 任意字符串
*/
FUNCTION splitstr(v_str in VARCHAR2) RETURN chrs_tbl IS
i NUMBER;
BEGIN
v_desc_chrs_tbl := chrs_tbl();
i := 1;
v_length := LENGTH(v_str);
v_step := 1;
WHILE i <= v_length LOOP
v_desc_chrs_tbl.extend;
v_desc_chrs_tbl(v_desc_chrs_tbl.count) := substr(v_str, i, 1);
i := i + 1;
END LOOP;
RETURN v_desc_chrs_tbl;
END splitstr;
/* 按分割符分割字符串
v_str : 任意不超过 1000 个字符的字符串
v_separators : 分割符，如 ',' ',;' ',;|'
*/
FUNCTION splitstrbyseparators(v_str in VARCHAR2,
v_separators in VARCHAR2) RETURN chrs_tbl IS
i NUMBER;
j NUMBER;
BEGIN
v_desc_chrs_tbl := chrs_tbl();
v_length := LENGTH(v_str);
j := 1;
i := REGEXP_INSTR(v_str, '[' || v_separators || ']', j);
v_step := i - j;
IF i <= 0 THEN
v_desc_chrs_tbl.extend;
v_desc_chrs_tbl(v_desc_chrs_tbl.count) := v_str;
ELSE
WHILE i > 0 AND i <= v_length LOOP
v_desc_chrs_tbl.extend;
v_desc_chrs_tbl(v_desc_chrs_tbl.count) := SUBSTR(v_str, j, v_step);
j := j + v_step + 1;
i := REGEXP_INSTR(v_str, '[' || v_separators || ']', j);
v_step := i - j;
END LOOP;
IF j <= v_length THEN
v_desc_chrs_tbl.extend;
v_desc_chrs_tbl(v_desc_chrs_tbl.count) := SUBSTR(v_str, j);
END IF;
END IF;
RETURN v_desc_chrs_tbl;
END splitstrbyseparators;
FUNCTION concat(chrs in chrs_tbl) RETURN VARCHAR2 IS
v_str VARCHAR2(1000);
BEGIN
IF chrs.count > 0 THEN
FOR i IN 1 .. chrs.count LOOP
v_str := v_str || chrs(i);
END LOOP;
ELSE
v_str := '';
END IF;
RETURN v_str;
END;
PROCEDURE output(chrs in chrs_tbl) IS
BEGIN
IF chrs.count > 0 THEN
FOR i IN 1 .. chrs.count LOOP
dbms_output.put_line(chrs(i));
END LOOP;
dbms_output.put_line('--success.');
ELSE
dbms_output.put_line('--failure.');
END IF;
END;
end strutil;
当自定义分隔符分割时，限制是分割后的字符不能超过 100 个，总体长度不能 1000 个。实际中，根据你系统的情况定制一个就行。个人觉得，没必要写通用的。
结果
字符串单个分割：
SQL> exec strutil.output(strutil.splitstr('a,1,b,2,ccdd;ef;'));
a
,
1
,
b
,
2
,
c
c
d
d
;
e
f
;
--success.
PL/SQL procedure successfully completed
SQL>
分割符为 "," 的分割结果：
SQL> exec strutil.output(strutil.splitstrbyseparators('a,1,b,2,ccdd;ef;', ','));
a
1
b
2
ccdd;ef;
--success.
PL/SQL procedure successfully completed
SQL>
分割符为 ",;" 的分割结果：
SQL> exec strutil.output(strutil.splitstrbyseparators('a,1,b,2,ccdd;ef;', ',;'));
a
1
b
2
ccdd
ef
--success.
PL/SQL procedure successfully completed
SQL>
分割符为 ",;|\" 的分割结果：
SQL> exec strutil.output(strutil.splitstrbyseparators('a,1,b,2,;|\;ef;', ',;|\'));
a
1
b
2
ef
--success.
PL/SQL procedure successfully completed
SQL>
