SQL 中 INSTR 函数简介及 截取地址应用

一、基本语法与参数解析

​​语法​​：

INSTR(string1, string2 [, start_position [, nth_occurrence]])

• ​​参数说明​​：
  a.​​string1​​：源字符串（必选）。
  b.​​string2​​：需查找的子字符串（必选）。
  c.​​start_position​​（可选）：搜索起始位置，默认为1（从左到右）；若为负数，则从右向左计算位置。
  d.​​nth_occurrence​​（可选）：查找第几次出现，默认为1（首次出现），若为负数，如 -1，则表示从右向左查询。
• 示例​​：

-- 查找子字符串首次出现的位置
SELECT INSTR('helloworld', 'l') FROM DUAL; -- 返回3 
-- 从第3位开始查找子字符串
SELECT INSTR('Oracle Database', 'a', 3) FROM DUAL; -- 返回5 
-- 查找第2次出现的子字符串
SELECT INSTR('hello world', 'o', 1, 2) FROM DUAL; -- 返回5 

二、核心应用场景

1.字符串分割​​

• ​​提取分隔符间的内容​​：结合SUBSTR函数
  （1）截取居住地址到第四级（镇/乡/街道）

select 
              NVL(
                    CASE
                        WHEN INSTR(t.address, '镇', -1) > 0 THEN
                            SUBSTR(t.address, 1, INSTR(t.address, '镇', -1))
                        WHEN INSTR(t.address, '乡', -1) > 0 THEN
                            SUBSTR(t.address, 1, INSTR(t.address, '乡', -1))
                        WHEN INSTR(t.address, '街道', -1) > 0 THEN
                            SUBSTR(t.address, 1, INSTR(t.address, '街道', -1)+1)
                        
                        ELSE t.address
                    END,
                    '未知'
                ) AS  "address"

备注：从右向左搜索​​：INSTR(residence_address, ‘镇’, -1) 从字符串末尾向前查找最后一个“镇”的位置。

• 测试用例​​

输入示例	输出结果
广东省深圳市宝安区福永街道	广东省深圳市宝安区福永街道
湖南省长沙市长沙县黄花镇	湖南省长沙市长沙县黄花镇
北京市海淀区中关村大街	北京市海淀区中关村大街
上海市浦东新区张江镇科技园	上海市浦东新区张江镇

2. 动态条件筛选​​

• ​​模糊查询​​：替代LIKE实现动态条件。

SELECT * FROM employees 
WHERE INSTR(email, 'example.com') > 0;