一、find_in_set函数概述

find_in_set函数可以对数据表里面某一项由英文逗号隔开的的值（字符串）进行检索。
find_in_set(str,strlist)，str:要查询的字符串；strlist：由英文逗号隔开的字符串

与MySQL不同？

mysql中自带了find_in_set函数，直接可以使用这个函数；oracle没有这个函数，需要我们手动创建find_in_set函数


二、find_in_set函数如何查询数据

如图所示，表中有一列名为version_type的列，它的值都是由英文逗号隔开的(version_type:1,2,3)字符串组成。

1
2
3
4
5
// 在mysql中，我们可以使用find_in_set函数进行精准搜索,如下

select *  from test where find_in_set('1',version_type);

// 这条语句可以查询出[列version_type]包含1的数据行

  • 为什么不使用in或like
    不使用in的原因：因为使用in时，必须是version_type的列值等于1才能查询出数据；
    不使用like的原因：而使用like的话，它的匹配方式更广泛，在检索是只要包含这个字符都会返回，我们需要精确取到1这个值，like会把11、21、111等有1在的值都匹配，故而不能lik

  • find_in_set函数 blue
    只能搜索逗号隔开的具体的值，比like更精准，必须是1或2或3这样的形式，故而在需要得到包含1的值时find_in_set函数更适合。



三、在oracle中创建find_in_set函数

1
CREATE OR REPLACE FUNCTION FIND_IN_SET(piv_str1 varchar2,  piv_str2 varchar2,  p_sep varchar2 := ',')

默认是逗号隔开，也可以替换为其它的字符隔开。如：p_sep varchar2 := '|'

⚠️：如果是换成其它字符隔开，则查询时需要传入隔开的字符，详细请看oracle使用方法

创建函数的sql如下： 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
CREATE OR REPLACE FUNCTION FIND_IN_SET(piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2 := ',')

RETURN NUMBER IS      
  l_idx    number:=0; -- 用于计算piv_str2中分隔符的位置  
  str      varchar2(500);  -- 根据分隔符截取的子字符串  
  piv_str  varchar2(500) := piv_str2; -- 将piv_str2赋值给piv_str  
  res      number:=0; -- 返回结果  
  loopIndex number:=0;

BEGIN  

-- 如果piv_str中没有分割符，直接判断piv_str1和piv_str是否相等，相等 res=1  
IF instr(piv_str, p_sep, 1) = 0 THEN  

   IF piv_str = piv_str1 THEN   
      res:= 1;  
   END IF;  

ELSE  

-- 循环按分隔符截取piv_str  
LOOP  
    l_idx := instr(piv_str,p_sep);  
     loopIndex:=loopIndex+1;

-- 当piv_str中还有分隔符时  
      IF l_idx > 0 THEN  

   -- 截取第一个分隔符前的字段str  
         str:= substr(piv_str,1,l_idx-1);  

   -- 判断 str 和piv_str1 是否相等，相等 res=1 并结束循环判断  
         IF str = piv_str1 THEN   
           res:= loopIndex;  
           EXIT;  

         END IF;  
        piv_str := substr(piv_str,l_idx+length(p_sep));  

      ELSE  

   -- 当截取后的piv_str 中不存在分割符时，判断piv_str和piv_str1是否相等，相等 res=1  
        IF piv_str = piv_str1 THEN   
           res:= loopIndex;  
        END IF;  

        -- 无论最后是否相等，都跳出循环  
        EXIT;  
      END IF;  

END LOOP;  

-- 结束循环  
END IF;  

-- 返回res  
RETURN res;  
END FIND_IN_SET;



四、使用方法

数据示意如下：

1、在MySQL中

使用方法如下：

1
2
3
4
5

select *  from test where find_in_set('2',version_type);

// 查询列VERSION_TYPE包含2的数据行
// 查询结果为2行

2、在oracle中

使用方法如下：

1
2
3
4
5
6

select find_in_set('1','1,2,3,4')  from test
select * from test where find_in_set('1',version_type) > 0
  
// 查询列VERSION_TYPE包含1的数据行
// 查询结果为3行
如果函数使用了别的分隔符，则要传入分隔符，方法如下： 
1
2
3
4
5

select * from test where find_in_set('1',version_type,'|') > 0
  
// 查询列VERSION_TYPE包含1的数据行(数据由”｜“分隔)
// 查询结果为3行




借鉴链接

oracle FIND_IN_SET函数
mysql中find_in_set()函数的使用


声明

借鉴部分均注明了原文出处，可在文章的`借鉴链接`处获取原文出处
文中若内容有涉及原版权，请邮件联系elvin-chen@qq.com，涉及的相关文章或内容将会及时更改或取消发布