Oracle中如何提取匹配指定10位数字前缀的邮箱用户数据（解决substr查询无返回问题）

原查询无返回结果的常见原因：

• 字段存储存在不可见的前导/后置空格，截取后的前10位和目标值不匹配
• 存储的数字为全角字符，与半角的目标匹配值存在编码差异
• 实际存储的用户标识前缀和预期的三个数字串不一致

推荐查询写法 #

1. 前缀LIKE匹配（性能最优） #

完全适配“前10位为指定数字，后续内容任意变化”的匹配需求，且可命中字段上的B树索引，大数据量下查询效率更高：

SELECT *
FROM 目标表名
WHERE
  user_identifier LIKE '0123456789%'
  OR user_identifier LIKE '0234567980%'
  OR user_identifier LIKE '0234566666%';

注：Oracle中%为通配符，可匹配任意长度、任意内容的后续字符串，覆盖不同邮箱域名的场景。

2. 带字段清洗的匹配写法 #

如果确认字段存在前后空格的脏数据，可以先清洗再匹配，避免不可见字符导致的匹配失效：

SELECT *
FROM 目标表名
WHERE
  TRIM(user_identifier) LIKE '0123456789%'
  OR TRIM(user_identifier) LIKE '0234567980%'
  OR TRIM(user_identifier) LIKE '0234566666%';

匹配失败排查方法 #

如果上述写法仍无返回结果，可执行以下语句查看字段实际存储的前10位内容，逐字符和目标值比对定位问题：

SELECT DISTINCT SUBSTR(TRIM(user_identifier), 1, 10) AS 提取前缀
FROM 目标表名
WHERE user_identifier IS NOT NULL;

补充说明：最初使用的SUBSTR(user_identifier,1,10) IN (...)语法在Oracle中完全合法，无返回结果基本都是字符层面的不匹配导致；但因为对字段做了函数转换，会导致字段上的普通索引失效，大表查询时性能明显低于LIKE前缀匹配的写法。

内容的提问来源于stack exchange，提问作者Mariana