表连接时触发ORA-01722无效数字错误，求解决方案

错误根源 #

ORA-01722 是由于隐式类型转换失败导致的：连接条件 t.phone = t1.phone 中，两侧字段类型不匹配（一侧为数字、一侧为字符串），Oracle 会自动尝试将字符串转换为数字，若字符串中包含非数字字符，就会触发该错误。

另外你的原 SQL 存在语法错误：CTE 的 WHERE 子句中使用了未定义的别名 t，应该改为表别名 p。

具体解决步骤 #

1. 确认字段类型 #

先查询两个表中 phone 字段的实际类型：

desc PHONE_TABLE;
desc PHONE_TABLE1;

重点确认 PHONE_TABLE1.phone 的类型（是数字还是字符串）。

2. 统一连接字段的类型 #

根据字段类型调整处理逻辑：

• 如果 PHONE_TABLE1.phone 是数字类型：
  确保处理后的 t1.phone 是纯数字后再转换为数字类型，同时过滤掉无法转换的记录：

  with cte1 as
  ( 
      select 
          p.*, 
          cast(regexp_replace(p.phone_number, '[^0-9]', '') as number) as phone
      from 
          PHONE_TABLE p
      where 
          p.phone_number is not null 
          -- 只保留能转换为数字的记录
          and regexp_like(regexp_replace(p.phone_number, '[^0-9]', ''), '^[0-9]+$')
      order by 
          p.phone_number asc
  )
  select * 
  from cte1 t1
  join PHONE_TABLE1 t on t.phone = t1.phone;
  

  这里用 [^0-9] 正则移除所有非数字字符，比原正则覆盖更全面。

• 如果 PHONE_TABLE1.phone 是字符串类型：
  直接将处理后的 t1.phone 保留为字符串类型，避免隐式转换：

  with cte1 as
  ( 
      select 
          p.*, 
          regexp_replace(p.phone_number, '[^0-9]', '') as phone
      from 
          PHONE_TABLE p
      where 
          p.phone_number is not null 
      order by 
          p.phone_number asc
  )
  select * 
  from cte1 t1
  join PHONE_TABLE1 t on t.phone = t1.phone;
  

3. 排查异常数据 #

如果仍有错误，可单独查询无法转换的异常数据：

select phone_number 
from PHONE_TABLE 
where not regexp_like(regexp_replace(phone_number, '[^0-9]', ''), '^[0-9]+$');

手动清理或修正这些异常数据即可。

内容的提问来源于stack exchange，提问作者Максим Майборода