文章摘要
这篇文章介绍了在Oracle中使用`regexp_substr`函数和自定义函数来分割字符串的技巧。主要内容包括：
1. **`regexp_substr`函数的使用**
- 方式1：使用固定分隔符（如逗号）分割字符串，例如：
```sql
select regexp_substr('bbb,aaa,ccc', '[^,]+', 1, level)
from dual
connect by level <= length('bbb,aaa,ccc') - length(replace('bbb,aaa,ccc', ',')) + 1;
```
- 方式2：动态计算分隔符数量，例如：
```sql
select regexp_substr(replace('aaa;bbb;ccb', '', ';'), '[^;]+', 1, level)
from dual
connect by level <= regexp_count('aaa;bb;', ';') + 1;
```
- 注意事项：在存储过程中需确保分割后没有空行。
2. **自定义函数**
- 创建自定义类型`result_split_list`，用于存储分割后的字符串。
- 定义自定义函数`split_strs`，接收字符串和分隔符，返回分割后的结果：
```sql
create or replace function split_strs(strs varchar2, type_split varchar2)
return result_split_list pipelined is
index_num pls_integer;
str_list varchar2(100) := strs;
begin
loop
index_num := instr(str_list, type_split);
if index_num > 0 then
pipe row(substr(str_list, 1, index_num - 1));
str_list := substr(str_list, index_num + length(type_split));
else
pipe row(str_list);
exit;
end if;
end loop;
return;
end split_strs;
```
- 示例效果：
```sql
select * from table (select split_strs('aaa,bbb,ccc', ',') from dual);
```
3. **处理分割后数据**
- 排除分割后的空行，例如：
```sql
select split_result, length(split_result) from (
select regexp_substr(replace('aaa;bb;', '', ';'), '[^;]+', 1, level) as split_result
from dual
connect by level <= regexp_count('aaa;bb;', ';') + 1
) where split_result is not null;
```
文章总结了在Oracle中高效分割字符串的实用方法，包括`regexp_substr`函数和自定义函数的使用技巧，帮助读者更好地处理和分析分割后的数据。
目录1. 使用 regexp_substr() 函数1.1 方式11.2 方式21.3 注意问题（尤其是存储过程中用到，可能会入坑）2. 自定义函数2.1 自定义类型 table2.2 自定义函数2.2.1 自定义函数2.2.2 效果如下：2.3 Oracle查看＜Collection＞ 类型数据总结
如下：
select regexp_substr(‘bbb,aaa,ccc’, ‘[^,]+’, 1, level) as split_result
from dual
connect by level <= length(‘bbb,aaa,ccc’) – length(replace(‘bbb,aaa,ccc’, ‘,’)) + 1;
from dual
connect by level <= length(‘bbb,aaa,ccc’) – length(replace(‘bbb,aaa,ccc’, ‘,’)) + 1;
如下：
select regexp_substr(replace(‘aaa;bbb;ccb’, ”, ‘;’), ‘[^;]+’, 1, level) as split_result
from dual
connect by level <=regexp_count(‘aaa;bb;’, ‘;’) + 1;
from dual
connect by level <=regexp_count(‘aaa;bb;’, ‘;’) + 1;
或者 写法，如下
with temp as
(select ‘bbb,aaa,ccc’ as str from dual)
select regexp_substr(replace(str, ‘,’, ‘ ,’), ‘[^,]+’, 1, level)
from temp
connect by level <=regexp_count(str, ‘,’) + 1
(select ‘bbb,aaa,ccc’ as str from dual)
select regexp_substr(replace(str, ‘,’, ‘ ,’), ‘[^,]+’, 1, level)
from temp
connect by level <=regexp_count(str, ‘,’) + 1
问题情况（可能会出现空行），如下：
解决问题：
上述空行不是我们所需要的，所以排除即可，如下：
select split_result,length(split_result) from (
select regexp_substr(replace(‘aaa;bb;’, ”, ‘;’), ‘[^;]+’, 1, level) as split_result
from dual
connect by level <=regexp_count(‘aaa;bb;’, ‘;’) + 1)
where split_result is not null;
select regexp_substr(replace(‘aaa;bb;’, ”, ‘;’), ‘[^;]+’, 1, level) as split_result
from dual
connect by level <=regexp_count(‘aaa;bb;’, ‘;’) + 1)
where split_result is not null;
如下：
create or replace type result_split_list as table of varchar2(100);
2.2.1 自定义函数
如下：
create or replace function split_strs(strs varchar2, type_split varchar2)
return result_split_list
pipelined is
index_num pls_integer;
str_list varchar2(100) :=strs;
begin
loop
index_num :=instr(str_list, type_split);
if index_num > 0 then
pipe row(substr(str_list, 1, index_num – 1));
str_list :=substr(str_list, index_num + length(type_split));
else
pipe row(str_list);
exit;
end if;
end loop;
return;
end split_strs;
return result_split_list
pipelined is
index_num pls_integer;
str_list varchar2(100) :=strs;
begin
loop
index_num :=instr(str_list, type_split);
if index_num > 0 then
pipe row(substr(str_list, 1, index_num – 1));
str_list :=substr(str_list, index_num + length(type_split));
else
pipe row(str_list);
exit;
end if;
end loop;
return;
end split_strs;
2.2.2 效果如下：
如下：
上面的效果我们看到查看到的是＜Collection＞ 类型，不方便查看数据，处理如下：
select * from table (select split_strs(‘aaa,bbb,ccc’,’,’) from dual);
到此这篇关于Oracle中分割字符串的方法的文章就介绍到这了,更多相关Oracle分割字符串内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！
您可能感兴趣的文章:Oracle通过正则表达式分割字符串 REGEXP_SUBSTR的代码详解Oracle 两个逗号分割的字符串,获取交集、差集(sql实现过程解析)
© 版权声明
文章版权归作者所有，未经允许请勿转载。
