Oracle 中的 REPLACE 函数详解

REPLACE 是 Oracle 数据库中用于字符串替换的函数，它可以搜索并替换字符串中的指定子串。

基本语法

 REPLACE(string, search_string [, replacement_string])

参数说明

• string: 原始字符串（可以是列名、变量或字符串常量）

• search_string: 要查找的子串

• replacement_string (可选): 替换后的新子串。如果省略，则删除所有匹配的 search_string

使用示例

1. 基本替换

 SELECT REPLACE('Oracle Database', 'Database', 'DB') FROM dual;
 -- 结果: 'Oracle DB'

2. 删除字符（省略第三个参数）

 SELECT REPLACE('2023-01-01', '-') FROM dual;
 -- 结果: '20230101' (删除了所有'-')

3. 结合表列使用

 SELECT product_name, 
        REPLACE(product_name, 'Old', 'New') AS updated_name
 FROM products;

4. 多重替换（嵌套使用）

 SELECT REPLACE(REPLACE('ABC123XYZ', 'ABC', 'Prefix'), 'XYZ', 'Suffix') 
 FROM dual;
 -- 结果: 'Prefix123Suffix'

5. 大小写敏感

SELECT REPLACE('Oracle ORACLE oracle', 'ORACLE', 'DB') FROM dual;
-- 结果: 'Oracle DB oracle' (只有完全匹配的部分被替换)

特殊注意事项

1. REPLACE 函数是大小写敏感的

2. 如果 search_string 为空字符串('')，函数将返回原字符串

3. 如果 string 为 NULL，函数返回 NULL

4. 替换是全局的，会替换所有出现的 search_string

与 TRANSLATE 函数的区别

REPLACE 替换整个子串，而 TRANSLATE 是逐个字符替换：

SELECT REPLACE('ABC123', '123', 'XYZ') FROM dual;  -- 结果: 'ABCXYZ'
SELECT TRANSLATE('ABC123', '123', 'XYZ') FROM dual; -- 结果: 'ABCXYZ'
SELECT REPLACE('121314', '1', 'X') FROM dual;      -- 结果: 'X2X3X4'
SELECT TRANSLATE('121314', '1', 'X') FROM dual;     -- 结果: 'X2X3X4'

实际应用场景

1. 数据清洗（如去除特殊字符）

2. 标准化数据格式（如统一日期格式）

3. 敏感信息脱敏

4. 动态SQL生成

-- 示例：电话号码格式化
SELECT REPLACE(REPLACE(phone_number, '(', ''), ')', '') AS clean_phone
FROM customers;

-- 示例：数据脱敏
SELECT REPLACE(credit_card, SUBSTR(credit_card, 5, 8), '****-****') 
FROM payment_info;

REPLACE 函数在数据处理和转换中非常实用，是 Oracle SQL 字符串处理的重要工具之一。