问oracle 中的 instr 函数应用 字符串查找函数实战教程

oracle 中的 instr 函数，作用是在目标字符串里面查找指定子串，返回子串第一次出现的位置下标，下标是从 1 开始计数，如果没有找到子串，函数返回数字 0，这个函数属于 Oracle 内置字符函数，经常搭配 where 条件做模糊筛选，和 like 模糊查询可以实现类似效果，但是 instr 灵活性更强，还可以指定起始查找位置，指定查找第几次出现，很多开发在处理编号 地址 备注这类文本字段的时候，都会使用 instr，需要重点记住，Oracle 的 instr 下标从 1 开始，这点和很多编程语言从 0 开始不一样，新手很容易在这里出错，同时 instr 默认区分大小写，大写小写不会匹配成功，函数支持四个参数，掌握参数含义之后，就可以完成各类文本定位 数据过滤 截取前置处理等业务场景，下面讲解语法参数，基础示例，常见业务用法还有注意事项。

一 函数语法与参数说明

语法格式

instr(原字符串, 查找子串, 起始位置, 第几次出现)

返回值规则

1 找到子串，返回对应的位置数字

2 没有匹配到子串，返回 0

3 原字符串或者子串为 null，函数返回 null

二 基础入门示例

select instr('abcdef','c') from dual;

执行结果是 3，字符 c 在字符串 abcdef 里面，第 3 位

select instr('ababab','ab',1,2) from dual;

起始位置是 1，查找第 2 次出现 ab 的位置，返回 3

select instr('hello world','x') from dual;

没有找到 x，返回 0

三 核心业务应用场景

场景 1 where 条件筛选包含指定字符的数据

这个是最常用用法，instr 大于 0 就代表包含子串，等价 like 效果

select from user_info where instr(address,'街道') > 0;

含义：筛选 address 字段里面包含街道两个字的记录

对比 like 写法 where address like '% 街道 %'，两种写法都可以，大数据量下 instr 性能有时候优于 like

场景 2 判断字符串是否不包含指定内容

select from user_info where instr(address,'街道') = 0;

含义：address 字段里面不存在街道，也就是不包含该子串

场景 3 从右侧反向查找子串

起始位置填写负数，代表从字符串末尾反向开始检索

select instr('a-b-c-d','-',-1,1) from dual;

从最右侧开始查找第一个横杠的位置，常用于获取最后一个分隔符位置，配合 substr 做字符串截取

场景 4 提取分隔符前后内容

业务里编号经常用横杠分隔，用 instr 定位分隔符，搭配 substr 截取前后文字

select substr(code,1,instr(code,'-')-1) 前缀,

substr(code,instr(code,'-')+1) 后缀

from product;

定位横杠位置，把编号拆分成前缀和后缀两部分

场景 5 查找子串第 N 次出现位置

select instr('1,2,3,4',',',1,3) from dual;

查找第三个逗号的位置，适合多层分隔文本解析

四 重要注意事项

第一 默认区分大小写，instr ('ABC','a') 返回 0，如果需要不区分大小写，可以配合 upper 或者 lower 函数，例如 instr (upper (name),'A')

第二 下标从 1 开始，不是从 0 开始，写代码的时候不要套用其他语言习惯

第三 如果起始位置大于原字符串总长度，函数返回 0

第四 instr 只做精确子串匹配，不支持通配符，这点和 like 不一样，like 支持 % _通配符，instr 不支持

第五 长文本大表查询，instr 仍然属于全表扫描，不能自动走索引，如果高频查询，需要考虑全文索引优化

五 instr 和 like 对比表

掌握 oracle 中的 instr 函数之后，就可以独立完成文本定位 筛选 拆分等业务 SQL，经常和 substr trim replace 这类字符函数组合使用，是 Oracle 开发非常高频的基础函数。

【常见问题】

问题 1 oracle 中的 instr 函数返回 0 代表什么意思

回答 1 oracle 中的 instr 函数返回 0，代表原字符串里面没有找到指定子串，在业务筛选的时候，经常用 instr 大于 0 判断是否包含目标文本，这是 oracle 中的 instr 函数最基础判定逻辑

问题 2 oracle 中的 instr 函数下标是从 0 开始吗

回答 1 oracle 中的 instr 函数下标是从 1 开始计数，不是从 0 开始，很多新手容易混淆，这是 oracle 中的 instr 函数非常关键的特点

问题 3 oracle 中的 instr 函数可以不区分大小写吗

回答 3 oracle 中的 instr 函数默认区分大小写，想要实现不区分大小写匹配，可以把原字符串和查找子串统一转为大写或者小写，搭配 upper lower 函数，实现 oracle 中的 instr 函数忽略大小写检索

问题 4 oracle 中的 instr 函数和 like 哪个性能更好

回答 4 oracle 中的 instr 函数和 like 普通模糊查询，默认都是全表扫描，性能差距不大，但是 oracle 中的 instr 函数支持反向查找指定第 N 次匹配，功能比 like 更强大，适合需要定位字符位置的业务

【免责申明】

本教程仅作为 Oracle 函数学习参考，SQL 在生产大表执行前建议评估性能，不同 Oracle 版本函数行为一致，但是业务 SQL 执行产生锁表全表扫描等影响，相关后果由操作者自行承担。