Oracle拆分split字符串，instr函数的使用

需求：

学生表中的课程字段是存课程表的id：1,2,3

学生表: student

………………
id    name     Course
1     张三      1，2，3

………………

课程表: course

………………
     id    name
     1       语文
     2       数字
     3       英语
………………

查询学生表时，需要将对应的课程名称关联查出，如：

………………

张三    语文,数字,英语

………………

 

实现方案-1：

先将学生表课程的值按逗号拆分；再利用oracle合并列的函数wm_concat合并。

 

利用正则表达式实现拆分的示例：

--利用正则表达式实现拆分的示例
SELECT REGEXP_SUBSTR ('abc1,cbd2,db3,db5', '[^,]+', 1,rownum) FROM DUAL
CONNECT BY ROWNUM <= LENGTH ('abc1,cbd2,db3,db5') - LENGTH (REPLACE ('abc1,cbd2,db3,db5', ',', ''))+1;

 实现:

select s.name
	,(
		select wm_concat(c.name) 
		  from course c
		 where c.id in 
			(
			 SELECT REGEXP_SUBSTR (s.course, '[^,]+', 1,rownum) as id FROM DUAL
			 CONNECT BY ROWNUM <= LENGTH (s.course) - LENGTH (REPLACE (s.course, ',', ''))+1
			)
	) as course
from student s
where s.name = '张三';

 

实现方案-2：

利用instr函数；再利用oracle合并列的函数wm_concat合并。

select s.name
	,(
		select wm_concat(c.name) 
		  from course c 
		 where instr(s.course, c.id) > 0
	) as course
FROM student s where name ='张三';

 ***此方法的弊端:当course.id存在数值较小的情况时就会有误.

    eg: student.course='12345,67890'  course.id=1

        这时就会错误的把course.id=1的记录查出来。

 

 

instr函数，判断源字符串'是否包含 '目标字符串'：

instr(sourceString,destString,start,appearPosition)

instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）

 

start代表查找的开始位置，这个参数可选的，默认为1，如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算；

appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1

返回值为：查找到的字符串的位置。

--instr函数可以代替like使用，在大数据量的搜索时，instr效率明显高于like
instr('oracle’,'ac’)>0 相当于 like '%ac%'
instr('oracle’,'ac’)=0 相当于 not like '%ac%'