Oracle学习笔记：instr结合substr实现split功能

一、instr()函数：字符查找函数

语法：

instr(string, str) -- instr(源字符串, 目标字符串)
instr(string, str, start_position, nth_appearance) -- instr(源字符串, 目标字符串, 起始位置, 匹配序号)

注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

实例：

select instr('hello', 'l') from dual;  -- 3 默认第1次出现的位置
select instr('hello', 'lo') from dual; -- 4 同时出现
select instr('helloworld', 'wo') from dual; -- 6 w开始的位置

select instr('helloworld', 'l', 2, 2) from dual;  -- 4 第2位开始，查找第2次出现的位置
select instr('helloworld', 'l', 3, 2) from dual;  -- 4 
select instr('helloworld', 'l', 4, 2) from dual;  -- 9
select instr('helloworld', 'l', -1, 1) from dual; -- 倒数第1位开始 往回查找第1次出现
select instr('helloworld', 'l', -2, 2) from dual; -- 4
select instr('helloworld', 'l', 2, 3) from dual;  -- 9

MySQL： select * from tableName where name like '%helloworld%';
Oracle：select * from tableName where instr(name,'helloworld')>0;  --这两条语句的效果是一样的

有点奇思妙想！

二、substr()函数：字符截取函数

语法：

substr(string string, int a, int b) -- substr(源字符串, 开始位置, 字符串长度)
substr(string string, int a) -- substr(源字符串, 开始位置) 截取开始位置后的所有字符串

实例：

select substr('HelloWorld!',3,3) from dual;  -- llo
select substr('HelloWorld!',2) from dual;  -- elloWorld!
select substr('HelloWorld!',-1) from dual; -- 倒数开始 往后取

三、instr结合substr实现split功能

利用 instr 定位分隔字符在字符串中的位置，然后利用 substr 进行切割即可。具体看操作。

-- 创建测试表
create table temp_cwh_test
(
 label varchar2(50)
);
-- 插入数据
insert into temp_cwh_test values('aa@@bbb@@cccasdf@@adsfedsf@@adsfeg');
insert into temp_cwh_test values('1@@22@@3@@444@@55');
insert into temp_cwh_test values('1@@2@@3@@4@@55');
-- 结果
select * from temp_cwh_test;
-- 1	aa@@bbb@@cccasdf@@adsfedsf@@adsfeg
-- 2	1@@22@@3@@444@@55
-- 3	1@@2@@3@@4@@55

-- 测试 --
select instr(label,'@@') from temp_cwh_test
-- 返回：3 2 2 即@@字符第一次出现时的位置

-- 最终实现 --
select substr(label,1,instr(label,'@@')-1) as label_001,
       substr(label,instr(label,'@@')+2,instr(label,'@@',1,2)-instr(label,'@@',1,1)-2) as label_002,
       substr(label,instr(label,'@@',1,2)+2,instr(label,'@@',1,3)-instr(label,'@@',1,2)-2) as label_003,
       substr(label,instr(label,'@@',1,3)+2,instr(label,'@@',1,4)-instr(label,'@@',1,3)-2) as label_004,
       substr(label,instr(label,'@@',1,4)+2) as label_005      
from temp_cwh_test;
/**
1	aa	bbb	cccasdf	adsfedsf	adsfeg
2	1	22	3	444	55
3	1	2	3	4	55
/

举个例子，查找第1个出现的@@，再查找第2个@@，截取中间部分即是所求。

参考链接1：Oracle中的instr()函数详解及应用

参考链接2：Oracle中的substr()函数详解及应用

参考链接3：oracle把一列中数据分割成多列 sql