Oracle数据库中动态OTP掩码替换的解决方案求助

问题背景 #

数据库中CTB.CTB_SMS_HISTORY表的SMS_TO_CUSTOMER字段存储了嵌入4位OTP的短信内容，OTP位置不固定，需要将4位OTP的中间两位替换为**（如7466改为7**6）。

分数据库解决方案 #

Oracle数据库 #

利用REGEXP_REPLACE函数匹配并替换4位数字：

SELECT 
    id,
    REGEXP_REPLACE(SMS_TO_CUSTOMER, '(\d)\d{2}(\d)', '\1**\2') AS masked_sms
FROM CTB.CTB_SMS_HISTORY
WHERE id = '51684';

• 正则说明：(\d)捕获第一位数字，\d{2}匹配中间两位，(\d)捕获最后一位；替换时用\1**\2保留首尾数字，替换中间两位为**。

MySQL 8.0+ #

使用REGEXP_REPLACE函数，注意转义符和捕获组语法：

SELECT 
    id,
    REGEXP_REPLACE(SMS_TO_CUSTOMER, '(\\d)\\d{2}(\\d)', '$1**$2') AS masked_sms
FROM CTB.CTB_SMS_HISTORY
WHERE id = '51684';

SQL Server #

通过PATINDEX定位4位数字起始位置，结合STUFF函数完成替换：

SELECT 
    id,
    STUFF(SMS_TO_CUSTOMER, PATINDEX('%[0-9][0-9][0-9][0-9]%', SMS_TO_CUSTOMER) + 1, 2, '**') AS masked_sms
FROM CTB.CTB_SMS_HISTORY
WHERE id = '51684'
AND PATINDEX('%[0-9][0-9][0-9][0-9]%', SMS_TO_CUSTOMER) > 0;

精准匹配优化（避免误替换） #

如果OTP有固定上下文（如示例中的one password that is ），可通过上下文缩小匹配范围，防止替换其他无关4位数字：

-- Oracle示例
SELECT 
    id,
    REGEXP_REPLACE(SMS_TO_CUSTOMER, 'one password that is (\d)\d{2}(\d)', 'one password that is \1**\2') AS masked_sms
FROM CTB.CTB_SMS_HISTORY
WHERE id = '51684';

内容的提问来源于stack exchange，提问作者Burak Çiv