在SQL查询场景中，定位指定字符或子字符串在目标字段中的出现位置是常见需求，INSTR和LOCATE函数是实现该需求的核心工具，不同数据库对这两个函数的支持情况和使用语法存在一定差异。
INSTR函数的基本用法
INSTR函数主要用于返回子字符串在源字符串中第一次出现的位置，不同数据库的语法略有区别。在MySQL和Oracle中，INSTR函数的语法结构为INSTR(str, substr)，其中str是源字符串，substr是要查找的子字符串，返回的位置从1开始计数，如果未找到则返回0。
以下是MySQL中使用INSTR函数查找字符位置的示例：
-- 查找字符b在字符串abcde中的位置
SELECT INSTR('abcde', 'b') AS position;
-- 结果返回2
-- 从指定位置开始查找，语法为INSTR(str, substr, start_position)
-- 从第3位开始查找字符c在字符串abccde中的位置
SELECT INSTR('abccde', 'c', 3) AS position;
-- 结果返回3
LOCATE函数的基本用法
LOCATE函数同样用于返回子字符串在源字符串中的出现位置，在MySQL中支持两种语法形式，一种是LOCATE(substr, str)，参数顺序和INSTR相反，另一种是LOCATE(substr, str, start_position)，支持指定起始查找位置。
MySQL中LOCATE函数的使用示例：
-- 基础用法，查找de在字符串abcde中的位置
SELECT LOCATE('de', 'abcde') AS position;
-- 结果返回4
-- 指定起始位置，从第2位开始查找c在字符串abccde中的位置
SELECT LOCATE('c', 'abccde', 2) AS position;
-- 结果返回3
两个函数的差异对比
INSTR和LOCATE函数虽然功能相似，但在使用场景和语法上存在明显区别，具体对比如下：
| 对比维度 | INSTR函数 | LOCATE函数 |
|---|---|---|
| 参数顺序 | 源字符串在前，子字符串在后 | 子字符串在前，源字符串在后 |
| 数据库支持 | 支持Oracle、MySQL等 | 主要支持MySQL，部分其他数据库不支持 |
| 起始位置参数 | 第三个参数为起始位置 | 第三个参数为起始位置 |
| 未找到返回值 | 返回0 | 返回0 |
实际应用场景示例
在实际业务中，查找字符位置通常用于字符串截取或者条件筛选，以下是两个常见场景的示例：
场景一：截取邮箱@符号前的用户名部分：
-- 使用INSTR函数实现
SELECT
email,
LEFT(email, INSTR(email, '@') - 1) AS username
FROM user_table;
-- 使用LOCATE函数实现
SELECT
email,
LEFT(email, LOCATE('@', email) - 1) AS username
FROM user_table;
场景二：筛选包含特定字符且字符位置大于5的记录：
-- 查找content字段中字符error出现位置大于5的记录
SELECT *
FROM log_table
WHERE INSTR(content, 'error') > 5;
-- 等价LOCATE写法
SELECT *
FROM log_table
WHERE LOCATE('error', content) > 5;
注意事项
- 两个函数的位置计数都是从1开始，不是从0开始，编写逻辑时需要注意偏移量的计算。
- 如果要查找的子字符串为空字符串，不同数据库的返回值可能存在差异，建议提前测试对应数据库的返回规则。
- 在跨数据库开发时，优先确认目标数据库是否支持对应的函数，避免语法报错。
- 如果源字符串或者子字符串为NULL，函数返回值通常为NULL，需要在查询中做好空值处理。