[大数据]【Oracle】字符串查找，只要23不要234

今日用比较优雅的方法解决了一个挺恼人的问题，特记录之。

1 背景

学生表 studeng?有字段：

id, name, courseIDs

分别对应?

ID，学生姓名，学生选择的课程ID串

23 |? 张三 |? ‘234, 235’

24 |? 李四 |? '11,12,13'

课程表 course 有字段：

id, name, score

分别对应

ID，课程名称，课程成绩

23? ?| 大学语文 | 99

234? ?| 大学物理 | 100

235? ?| 大学英语 | 120

上面有2个表。学生表和课程表。

学生表的 courseIDs 里面保存着该学生选择的课程的 ID拼接出的字符串。这是比较常见的设计方式。

2 问题

存取都比较方便方便，但是有个问题。比如我们要

查找

所有选择

大学语文

的学生姓名

该如何写SQL语句呢？

3 探索

我在下面的 SQL 语句中，直接使用大学语文的课程 id，即 23 代表大学语文。

通过“大学语文”文字查找其课程 id 不是本文的重要内容。

首先，通过逻辑推算，我们知道问题中的查找结果应为 空结果集。

即，张三和李四都没有选择 大学语文课。

3.1 方法一，初级写法，使用 like 关键词

like 是 SQL 语法中的关键词，也是最常用的模糊查询方法。

select s.name 
from student s 
where 
s.courseIDs like '%23%'

查询结果：

张三

这明显是错误的，但是为什么会发生呢？

因为 like '%%' 是模糊查询，只要匹配就返回。

‘234, 235’ 是满足 like '%23%' 的要求的，因为里面有 23 。

这是很严重，也经常容易出现的错误。

3.2 方法二，使用?instr() 、substr()?函数

注意：instr() 、substr() 都是Oracle数据库软件的内置函数，并非标准的 SQL 语法关键词。也就是说用到这些函数的SQL语句，在MySQL和MS SQL Server是直接报语法错误、无法执行的，只能在 Oracle 数据库中使用。

题外话：其实在 MySQL 中，使用MySQL的内置函数 find_in_set 实现这个问题就特别简单，后面会讲。

这些函数的具体解释不多说了，简单说就是 instr() 返回目标字符串在源头字符串的位置，若查无结果，则返回0。例如：

select instr('helloworld','lo') from dual;? 返回结果：4 即：在“lo”中，“l”开始出现的位置。

用这个函数写出SQL语句：

select s.name 
from student s 
where 
instr(s.courseIDs, '23') != 0

查询结果：

张三

同样是错误的。其实 instr() 函数 和 like 关键词有些类似，都是只要出现 23 就认为匹配成功了。而我们要的是独立的23 ，而不是 234 里面的 23。

3.3 方法三，高阶玩法，正则表达式函数 regexp()

?很容易的，我们就想到 正则表达式 中的贪婪查询。

正则表达式 就是字符串匹配和查找的神，基本可以满足所有的查找和替换需求。

如果你是一名程序员，却不了解正则表达式，那你一定是一名不合格的程序员。

Oracle内置有四个正则表达式查询函数，非标准SQL语法。?

REGEXP_LIKE() ：与LIKE() 的功能相似
REGEXP_INSTR() ：与INSTR() 的功能相似
REGEXP_SUBSTR() ：与SUBSTR() 的功能相似
REGEXP_REPLACE() ：与REPLACE() 的功能相似

正则表达式用法我就不写了，反正就是很复杂。下面直接讲我的简单解决方案。

3.4 博主实现的简单方案

思路

其实我们需求很明确，就是只要 23 不要 234。就是如何精确的查找到 独立的 23。

我们观察 courseIDs 用逗号 “,”拼接出的字符串，这个逗号“,"本身就是个天然的分隔符，既然本身就有分隔符，为什么查询的时候不可以用呢？

阻碍1

可以使用? 23,? 来查询，但是 couseIDs 字符串? 234,235? 最后末尾少了个逗号啊。这个表的数据我没有权限修改，不能在数据后面加逗号。

并且如果大学语文是 34，那我用? ?34,? ?来搜索? 234,235? ，依然是查找成功，即查询结果依然会返回张三，说明这个查询策略是有严重BUG的。

阻碍2

那我前面加个逗号，使用?,23, 来查询呢。但是? couseIDs 字符串 234,235? ?不仅末尾少了个逗号，最前面也没有逗号，碰到查询第一个和最后一个ID就会查询不到。我们查询? ,234,? 就会遗漏了张三。怎么办呢？

解决

我们无需修改数据库表中的数据，只需要在SQL语句中在字段内容前后 拼接上一前一后两个逗号就行了。

查询得知，Oracle有个运算符 “||”可以拼接字符串。于是，有下面的SQL：

select s.name 
from student s
where 
instr(
','||s.courseIDs||',' 
,
',23,') != 0

原理很简单：在 s.courseIDs 即? 234,235??的首尾各加一个 逗号（变成?,234,235, ），然后再使用 instr() 函数查询该字符串? ?,234,235, ?里面是否有 ,23,??。这样就可以使用天然的逗号分隔符，把每个独立的ID数字都独立的包裹起来。

限制条件

很明显，这个方法必须在该字段的数据非常规整的前提下使用的。如果数字与逗号之间出现空格或空白字符，会查询失败。如? ,56? ?,? 88,? ?可能就无法查找到? ,56,? 和?,88,???

<全文完>

写文不易，就这一行语句写了近三个小时。如果有收获请点赞支持。

全博所有文章欢迎转载，转载注明出处即可。