Oracle SQL: TRANSLATE 和 REGEXP_LIKE 的知识点详细分析

前言

🤟 找工作，来万码优才：👉 #小程序://万码优才/r6rqmzDaXpYkJZF

1. TRANSLATE

TRANSLATE 用于替换字符串中指定字符集的每个字符，返回替换后的字符串

逐一映射输入字符串的字符到目标字符集，没有提供复杂的模式匹配功能

语法：

TRANSLATE(string, from_string, to_string)

• string：需要替换的原字符串
• from_string：要被替换的字符集
• to_string：替换后的字符集

注意：from_string 和 to_string 是按位置一一对应的，to_string 中没有对应字符的位置会被删除

特点：

• TRANSLATE 不支持正则表达式，仅支持简单的字符替换
• 如果 from_string 的某个字符没有对应的 to_string 字符，替换后的结果会删除该字符。
  替换后的结果可以为空字符串，但不会返回 NULL

整体Demo如下：

-- 示例 1: 基础字符替换
SELECT TRANSLATE('123ABC', '123', 'XYZ') AS RESULT FROM DUAL;
-- 输出: 'XYZABC'-- 示例 2: 删除字符
SELECT TRANSLATE('123ABC', '123', '') AS RESULT FROM DUAL;
-- 输出: 'ABC'-- 示例 3: 替换多个字符
SELECT TRANSLATE('HELLO WORLD', 'HEL', 'XYZ') AS RESULT FROM DUAL;
-- 输出: 'XYZO WORLD'

截图如下：

2. REGEXP_LIKE

REGEXP_LIKE 用于对字符串进行正则表达式匹配判断，返回布尔值（TRUE 或 FALSE）

是 Oracle 提供的正则表达式匹配函数，可以处理复杂的字符串模式

特别说明的是

REGEXP_LIKE 在 Oracle 中是一个布尔函数，只能用在条件上下文（如 WHERE、CASE）中，不能直接作为一个结果列返回

基本语法：

REGEXP_LIKE(string, pattern [, match_parameter])

基本参数如下：

• string：需要匹配的字符串
• pattern：正则表达式
• match_parameter：可选，用于指定匹配行为（如大小写敏感等）
  ‘i’：忽略大小写
  ‘c’：区分大小写（默认）
  ‘n’：允许匹配字符串中的换行符
  ‘m’：启用多行模式

错误Demo：

-- 示例 1: 检查字符串是否仅由数字组成
SELECT REGEXP_LIKE('12345', '^[0-9]+$') AS IS_NUMERIC FROM DUAL;-- 示例 2: 检查字符串是否包含字母 'A'
SELECT REGEXP_LIKE('123A45', '[A-Za-z]') AS CONTAINS_ALPHA FROM DUAL;-- 示例 3: 匹配复杂模式（检查是否为邮箱格式）
SELECT REGEXP_LIKE('[e-mail]', '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') AS IS_EMAIL FROM DUAL;-- 示例 4: 使用匹配参数忽略大小写
SELECT REGEXP_LIKE('Oracle', '^oracle$', 'i') AS IGNORE_CASE_MATCH FROM DUAL;

会输出如下：ORA-00904: "REGEXP_LIKE": invalid identifier

正确Demo：

在 WHERE 子句中使用 REGEXP_LIKE：

-- 示例 1: 检查字符串是否仅由数字组成
SELECT '12345' AS INPUT_VALUE
FROM DUAL
WHERE REGEXP_LIKE('12345', '^[0-9]+$');
-- 输出: '12345'

截图如下：

使用 CASE 包装 REGEXP_LIKE：

-- 示例 2: 检查字符串是否包含字母 'A'
SELECT CASE WHEN REGEXP_LIKE('123A45', '[A-Za-z]') THEN 'TRUE'ELSE 'FALSE'END AS CONTAINS_ALPHA
FROM DUAL;
-- 输出: TRUE

检查复杂模式（如邮箱格式）

-- 示例 3: 验证是否为邮箱格式
SELECT CASE WHEN REGEXP_LIKE('[e-mail]', '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') THEN 'TRUE'ELSE 'FALSE'END AS IS_EMAIL
FROM DUAL;
-- 输出: TRUE

忽略大小写匹配

-- 示例 4: 区分大小写和忽略大小写
SELECT CASE WHEN REGEXP_LIKE('Oracle', '^oracle$', 'i') THEN 'TRUE'ELSE 'FALSE'END AS IGNORE_CASE_MATCH
FROM DUAL;
-- 输出: TRUE

特别注意：

• REGEXP_LIKE 是布尔型函数，因此在 SELECT 列表中无法直接输出 TRUE 或 FALSE，需要通过 CASE 转换为可显示的字符串结果
• 如果在 WHERE 子句中使用，只有匹配成功的记录会返回结果

3. 实战

实战中发现两个字段的格式都不一样：

具体差异如下：

查询内容	第一条查询 TRANSLATE	第二条查询 REGEXP_LIKE
功能差异	替换掉所有数字后检查是否为空字符串	检查字符串是否完全匹配数字正则表达式
处理空格	不处理空格，空格被保留	使用 TRIM 去掉空格后进行匹配
处理特殊字符	替换后仍存在非数字字符会返回非空字符串，不符合计数条件	如果存在特殊字符，直接无法匹配
性能差异	TRANSLATE 速度更快，适合简单字符替换	REGEXP_LIKE 功能更强大，但性能可能稍差
可能的差异	会忽略空格和部分特殊字符的影响，统计结果可能更大	精确匹配数字，统计结果可能更小

实际结果的比较：

DEVICE_ID	第一条结果 (TRANSLATE)	第二条结果 (REGEXP_LIKE)
123456	计入	计入
123456 (多个空格)	不计入	计入 （通过TRIM）
abc123	不计入	不计入
000	计入	计入
123@456	不计入	不计入

如果 DEVICE_ID 存在空格或特殊字符，REGEXP_LIKE 会更精确
如果 DEVICE_ID 仅考虑数字部分，TRANSLATE 会更宽松一些