Oracle中使用SQL提取字段值中所有数字'1'的实现方法

注意：TABLE是Oracle保留关键字，实际业务中如果你的表名/字段名使用了该关键字，查询时需要用双引号包裹转义。下文示例将测试表命名为test_tbl，存储待处理值的字段命名为val_col，避免关键字冲突。

需求对应关系 #

待处理字段值和预期返回结果的对应规则如下：

• 输入1000 → 输出1
• 输入1001 → 输出11
• 输入1010 → 输出11
• 输入0100 → 输出1

可直接运行的实现方案 #

方案1：正则替换写法（兼容性好，代码易读） #

核心逻辑是通过正则把所有不是'1'的字符全部替换为空，剩余内容就是原字符串里所有的'1'，支持Oracle 10g及以上版本：

SELECT
  val_col 原始值,
  REGEXP_REPLACE(val_col, '[^1]', '') 提取结果
FROM test_tbl;

方案2：TRANSLATE函数写法（性能更优，适合大表场景） #

如果表数据量很大，优先用TRANSLATE函数实现，不需要走正则匹配，执行效率更高：

SELECT
  val_col 原始值,
  TRANSLATE(val_col, '1' || val_col, '1') 提取结果
FROM test_tbl;

效果验证 #

你可以直接执行下面的语句，无需提前建表就能看到运行效果：

WITH test_tbl(val_col) AS (
  SELECT '1000' FROM DUAL UNION ALL
  SELECT '1001' FROM DUAL UNION ALL
  SELECT '1010' FROM DUAL UNION ALL
  SELECT '0100' FROM DUAL
)
SELECT
  val_col 原始值,
  REGEXP_REPLACE(val_col, '[^1]', '') 正则写法结果,
  TRANSLATE(val_col, '1' || val_col, '1') 函数写法结果
FROM test_tbl;

执行返回结果完全匹配预期：

两种写法都能正确处理字段值为字符串类型、数值类型的场景，不需要额外做类型转换。

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